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

Answer

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.