CODEWITHSUNDEEP
mysql
DrTas
DrTas

Reputation: 3

MySQL: Select newest with two matching fields

I need to get the newest records where two fields are matching

So, this data..

id |  a   |  b   | date         | label
---------------------------------------
1  |  10  |  15  |  954576000   |  hide
2  |  10  |  15  |  1097650800  |  show
3  |  20  |  25  |  1072252800  |  hide
4  |  20  |  25  |  1113202800  |  show
5  |  35  |  60  |  1027062000  |  show
6  |  39  |  63  |  1012464000  |  show

Should return this...

id |  a   |  b   | date         | label
---------------------------------------
2  |  10  |  15  |  1097650800  |  show
4  |  20  |  25  |  1113202800  |  show
5  |  35  |  60  |  1027062000  |  show
6  |  39  |  63  |  1012464000  |  show

This query is nice and simple and works great if I'm just checking the a field: SELECT * FROM mytable GROUP BY a HAVING MAX(date)

But I've been having a hard time figuring out how to get it to get the newest records when two fields match.

note: the date is in UNIX Time

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want the most recent record for "a", then use a join and group by:

select t.*
from table t join
     (select a, max(date) as maxdate
      from table t
      group by a
     ) tt
     on t.a = tt.a and t.date = tt.maxdate;

Your syntax with having max(date) doesn't work. It doesn't do what you think and it really doesn't make sense.

EDIT:

If you need to match two fields, then include them both in the subquery:

select t.*
from table t join
     (select a, b, max(date) as maxdate
      from table t
      group by a
     ) tt
     on t.a = tt.a and t.b = tt.b and t.date = tt.maxdate;

Upvotes: 1

Related Questions