Get ID of row in aggregate

I have the following query to get me the values corresponding with the latest date:

SELECT MAX(RowAddedDate), X, Y
FROM dbo.MyTable
GROUP BY X, Y

This is fine, but I need to get the ID of each row in this query. If I add the ID though, I get everything as the ID needs to be in the GROUP BY.

How do I solve this?

You can use window (ranking). functions for this:

WITH ct AS
  ( SELECT X, Y, RowAddedDate, ID,
           Rn = DENSE_RANK() OVER (PARTITION BY X, Y
                                   ORDER BY RowAddedDate DESC)
    FROM dbo.MyTable
  )
sELECT X, Y, RowAddedDate, ID
FROM ct 
WHERE Rn = 1 ;

If multiple rows have the same date with same X and Y, all of them will be returned due to the DENSE_RANK().

If you want only one, you can replace it with ROW_NUMBER() and adjust the ORDER BY to control which of the tied rows will be returned.

As for efficiency, an index on (X, Y, RowAddedDate) INCLUDE (ID) would help.

StackExchange Database Administrators Q#158602, answer score: 10