return rows of sql query with MAX(date) and GROUP BY id

Question

id      position      date
1          a          2012 01 12
2          a          2012 01 22
3          a          2012 01 22
1          b          2012 02 13
1          c          2012 02 22
2          b          2012 01 23

How can i get the row of records with MAX(date) that group by id

id      position      date
1          c          2012 01 22
2          b          2012 01 23
3          a          2012 01 22

Answer 1

SELECT t.id, t.position, r.Maxdate
FROM (
      SELECT id, MAX(date) as Maxdate
      FROM Yourtable
      GROUP BY id
) r
INNER JOIN Yourtable t
ON t.id = r.id AND t.date = r.Maxdate