CODEWITHSUNDEEP
mysqlgroup-bydistinct
wsams
wsams

Reputation: 2637

Select row with distinct value and max date

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

Upvotes: 4

Views: 12554

Answers (4)

Eric
Eric

Reputation: 11

When it is about getting the maximum date in a set of record, it is not advisable to combine max(date) with other rows. You will always get, of course, the max(date) but other fields will be the first occurrence in that set. Here is an answer to your question, using your table as an example:

+----+-----+---------------------+
| 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

Upvotes: 1

Anthony Accioly
Anthony Accioly

Reputation: 22461

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.

Upvotes: 1

itsmeee
itsmeee

Reputation: 1627

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;

Upvotes: 8

Sparky
Sparky

Reputation: 15075

Not sure, but based on your example, try this:

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

Upvotes: -1

Related Questions