Tsql group by max datetime in rows

Question

I have a table, and its data looks like this:

id name date --------- --------- ---------- 1 a 2012-08-30 10:36:27.393 1 b 2012-08-30 14:36:27.393 2 c 2012-08-30 13:36:27.393 2 d 2012-08-30 16:36:27.393 

I get the maximum datetime with this query:

 SELECT id,Max(date) as mymaxdate FROM table1 group by id 

This query will give me two lines:

 1 2012-08-30 14:36:27.393 2 2012-08-30 16:36:27.393 

This is correct, but how can I change it to get this result?

 1 b 2012-08-30 14:36:27.393 2 d 2012-08-30 16:36:27.393 

thanks

Answer 1

For SQL Server 2005+

 WITH cteMaxDate AS ( SELECT id, name, date, ROW_NUMBER() OVER(PARTITION BY id ORDER BY date DESC) AS RowNum FROM table1 ) SELECT id, name, date FROM cteMaxDate WHERE RowNum = 1; 

Answer 2

One of the options:

 select t1.id ,t1.name ,t1.date from table1 t1 inner join ( SELECT id,Max(date) as mymaxdate FROM table1 group by id ) mt1 on t1.id = mt1.id and t1.date = mt1.mymaxdate