Q:

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.

A:

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;

