Alex Alex - 4 months ago 8
MySQL Question

SQL query for same user with last date

I have a table with following data. ID, date and type are all primary key.
ID is person, type is for work type.

id date Type
--------------------------
6 | 2011 | 2
6 | 2012 | 2
6 | 2012 | 3
6 | 2012 | 5
7 | 2013 | 1
7 | 2016 | 1
etc..


I want to create a query that would show both id 6 that have difference type, and take the last date if there is tow row for same ID with same type.
The result should show same as that.

id date Type
--------------------------
6 | 2012 | 2
6 | 2012 | 3
6 | 2012 | 5
7 | 2016 | 1
etc..

Answer

You can do this with a correlated subquery:

select t.*
from t
where t.date = (select max(t2.date) from t t2 where t2.id = t.id);
Comments