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.