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