PocKeT PocKeT - 1 month ago 6
MySQL Question

MySQL select latest distinct records from user where with latest date

I have a table called "lift":

-----------------------------------------------------
| ID | USERNAME | lift_ID | DATE |
-----------------------------------------------------
| 1 | user1 | 0 | 2013-06-01 |
-----------------------------------------------------
| 2 | user1 | 0 | 2013-06-03 |
-----------------------------------------------------
| 3 | user1 | 1 | 2013-06-03 |
-----------------------------------------------------
| 4 | user2 | 0 | 2013-06-04 |
-----------------------------------------------------
| 5 | user2 | 1 | 2013-06-04 |
-----------------------------------------------------
| 6 | user2 | 1 | 2013-06-05 |
-----------------------------------------------------
| 7 | user1 | 2 | 2013-06-03 |


I want to select all distinct
lift_id
's from a certain user where the date is the latest of that
lift_id
of that user.

If I would do that for user1 I would get:

-----------------------------------------------------
| 2 | user1 | 0 | 2013-06-03 |
-----------------------------------------------------
| 3 | user1 | 1 | 2013-06-03 |
-----------------------------------------------------
| 7 | user1 | 2 | 2013-06-03 |


I've tried:

SELECT *
FROM lift l1
WHERE date` = (
SELECT MAX(date)
FROM lift
WHERE l1.lift_id = lift.lift_id)
AND username = 'user1'
ORDER BY lift_id ASC
)
;


That select's all distinct lifts on the max date but doesn't take account of the different users which is what I want.

Answer

Your query is close. You need an additional correlation clause in the subquery:

SELECT l.*
FROM lift l
WHERE l.date = (select max(l2.date)
                from lift l2
                 where l2.lift_id = l.lift_id and
                      l2.username = l.username
               ) and
      l.username = 'user1'
ORDER BY l.lift_id ASC;