Select row with distinct value and max date

Question

I have a MySQL (5.1.49) table with three columns.

mysql> create table testme(id int auto_increment, id2 int not null, somedate datetime, primary key(id));

In my case id2 is not unique, but I want to return rows with distinct id2 values with the max somedate.

Here's some sample data.

mysql> insert into testme values (1, 5, '2012-01-02 01:01:01'),(2, 5, '2012-02-02 02:02:02'),(3, 7, '2010-01-10 11:01:33');

This question almost answers mine, but with the extra id field, the returned id and id2 don't match. For id2=5, it's returning id=1 instead of id=2.

mysql> select id, id2, max(somedate) from testme group by id2;
+----+-----+---------------------+
| id | id2 | max(somedate)       |
+----+-----+---------------------+
|  1 |   5 | 2012-02-02 02:02:02 |
|  3 |   7 | 2010-01-10 11:01:33 |
+----+-----+---------------------+

I'm expecting,

+----+-----+---------------------+
| id | id2 | max(somedate)       |
+----+-----+---------------------+
|  2 |   5 | 2012-02-02 02:02:02 |
|  3 |   7 | 2010-01-10 11:01:33 |
+----+-----+---------------------+

Want the ID that matches maximum date for each ID2

Does anyone have any ideas please? Thanks

Answer 1

This query would definitely work, although it may be not the optimal one:

select t.id, s.id2, s.somedate 
from testme t 
  join 
    ( select id2, max(somedate) as somedate 
      from testme 
      group by id2
    ) s 
    on  s.id2 = t.id2 
    and s.somedate = t.somedate;

Answer 2

An alternative solution to your problem:

SELECT t0.id, t0.id2, t0.somedate 
FROM testme AS t0 
LEFT JOIN testme AS t1 
    ON t0.id2 = t1.id2 
    AND t1.somedate > t0.somedate
WHERE t1.id IS NULL;

Like @itsmeee solution, if id2, somedate pairs are not unique, it will bring both rows:

+----+-----+---------------------+
| id | id2 | somedate            |
+----+-----+---------------------+
|  4 |   8 | 2012-02-02 02:02:02 |
|  6 |   8 | 2012-02-02 02:02:02 |
+----+-----+---------------------+

If id2, somedate pairs are not unique and, for some reason, you need only one result per id2, you can let MySQL pick one for you with an extra GROUP BY t0.id2 clause. But be warned that this is non standard SQL behavior.