CODEWITHSUNDEEP
phpmysqlsqldateselect
Bram
Bram

Reputation: 47

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.

Upvotes: 1

Views: 85

Answers (2)

sagi
sagi

Reputation: 40481

You can use NOT EXISTS() :

SELECT t.* FROM lift t
WHERE NOT EXISTS(SELECT 1 FROM lift s
                 WHERE s.lift_id = t.lift_id 
                   AND s.username = t.username
                   AND s.date > t.date)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 4

Related Questions