Get the most recent entry for each group in sql server

Published: {ts '2020-08-04 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/get-the-most-recent-entry-for-each-group-in-sql-server/

A colleague asked this question the other day and it took me a moment to remember how to do this. The question was how do I get the most recent entry for each named group within a table.

To demonstrate the problem take this example dataset.

Sample Data

Id Name Date
1John2019-08-01
2John2019-07-01
3John2019-09-01
4Peter2020-07-01
5Peter2020-08-01

Desired Result

Id Name Date
3John2019-09-01
5Peter2020-08-01

Solution

While there is more than one way to skin a cat, my preferred way is to define groups by the name column and number the rows in each group based on their date column ordered. Then I can just select the first row in each group.

SELECT x1.* FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rowNumber FROM mytable ) AS x1 WHERE x1.rowNumber = 1

You could also write this as a CTE if you wanted.

WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY date DESC) AS rowNumber FROM mytable ) SELECT * FROM CTE WHERE rowNumber = 1