How to filter mysql duplicated id rows and retrieve the one with latest timestamp?

Question

I have a mysql table with entries like this:

id  name        value   date
1   results1    1000000 2010-06-02 01:31:12
2   results2    600000  2010-09-03 05:42:54
1   results1    1200000 2010-09-06 02:14:36

How can I SELECT all and filter multiple rows that have the same id, selecting only the one with latest date?

The "date" column datatype is timestamp and it has CURRENT_TIMESTAMP as default.

Answer 1

select m.*
from (
    select id, max(date) as MaxDate
    from MyTable
    group by id
) mm
inner join MyTable m on mm.id = m.id and mm.MaxDate = m.Date