Group by name and return row with most recent date

user_5877690 0
Viewed 36

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 |
+-------+--------+------------+
snowflake-cloud-data-platform sql
2 Answers

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;
user_18632290

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.

user_439920
Related