TOP 1 Request from each identifier with multiple instances

Question

This query will return the top for all rows in MS Access.

SELECT TOP 1 * FROM [table] ORDER BY table.[Date] DESC; 

I need to return the top date for each identifier, which may have multiple dates.

 ID DATE 1 01/01/2001 1 01/12/2011 3 01/01/2001 3 01/12/2011 

Should only return top dates like this.

 1 01/12/2011 3 01/12/2011 

Answer 1

You want to use the MAX function with GROUP BY .

 SELECT ID, MAX(DATE) FROM [table] GROUP BY ID