Group by name and return row with most recent date-CodePudding

Suppose you have the following data:

 ------- -------- ------------ 
| Name  | Rating |    Date    |
 ------- -------- ------------ 
| Alice |    4.5 | 01/01/2022 |
| Alice |      4 | 14/12/2021 |
| Alice |      4 | 16/05/2021 |
| Mary  |      5 | 05/01/2022 |
| Mary  |      4 | 31/01/2022 |
| Bob   |    3.5 | 03/02/2022 |
 ------- -------- ------------

What would be the best way to group by name and return the row with the most recent date in Snowflake (ANSI SQL)? Expected output:


 ------- -------- ------------ 
| Name  | Rating |    Date    |
 ------- -------- ------------ 
| Alice |    4.5 | 01/01/2022 |
| Mary  |      4 | 31/01/2022 |
| Bob   |    3.5 | 03/02/2022 |
 ------- -------- ------------

CodePudding user response：

With QUALIFY you can keep the newest per name

SELECT * 
FROM table
QUALIFY row_number() OVER (PARTITION BY name ORDER BY date desc) = 1;

As you will see in the doc's it's the same as Tim's answer without the need for the nested select.

CodePudding user response：

Using ROW_NUMBER:

WITH cte AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Date DESC) rn
    FROM yourTable t
)

SELECT Name, Rating, Date
FROM cte
WHERE rn = 1;