Select row with excellent value and maximum date

Question

I have a MySQL table (5.1.49) 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 different id2 values ​​with max somedate .

Here are 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 id is returned with an additional id field and id2 does not match. For id2 = 5, it returns 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 expect,

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

Want an ID that matches the maximum date for each ID2

Does anyone have any ideas? Thanks

Answer 1

This query will definitely work, although it may not be optimal:

 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

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's solution, if id2, somedate not unique, they will contain both lines:

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

If id2, somedate not unique and for some reason you only need one result for id2 , you can let MySQL choose one for you with the additional GROUP BY t0.id2 . But beware, this is non-standard SQL behavior.

Answer 3

When it comes to getting the maximum date in a recordset, it is not practical to combine max(date) with other strings. Of course, you will always get max(date) , but other fields will be the first occurrence in this set. Here is the answer to your question using an example table:

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

 select id, id2, somedate from testme t1 where somedate = (select max(somedate) from testme t2 where t2.somedate = t1.somedate) order by somedate desc 

Answer 4

Not sure, but based on your example, try the following:

 select max(id) as Id,id2,max(someDate) from testMe group by Id2 

Answer 5

 select id2, max(id), max(somedate) from testme group by id2;