Get the most recent entry for each group in sql server

Author: Steven Neiland
Published:

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

What Do You Think?

Reader Comments

Comment Etiquette:

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.
*
*



Archives Blog Listing