Published:
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
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 |
|---|---|---|
| 1 | John | 2019-08-01 |
| 2 | John | 2019-07-01 |
| 3 | John | 2019-09-01 |
| 4 | Peter | 2020-07-01 |
| 5 | Peter | 2020-08-01 |
Desired Result
| Id | Name | Date |
|---|---|---|
| 3 | John | 2019-09-01 |
| 5 | Peter | 2020-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
Reader Comments