CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Simon
Simon

Reputation: 1111

Group by name and return row with most recent date

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 |
+-------+--------+------------+

Upvotes: 1

Views: 74

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

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.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

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;

Upvotes: 2

Related Questions