Michal M Michal M - 12 days ago 8
MySQL Question

How to select only the latest rows for each user?

My table looks like this:

id | user_id | period_id | completed_on
----------------------------------------
1 | 1 | 1 | 2010-01-01
2 | 2 | 1 | 2010-01-10
3 | 3 | 1 | 2010-01-13
4 | 1 | 2 | 2011-01-01
5 | 2 | 2 | 2011-01-03
6 | 2 | 3 | 2012-01-13
... | ... | ... | ...


I want to select only the latest users periods entries, bearing in mind that users will not all have the same period entries.

Essentially (assuming all I have is the above table) I want to get this:

id | user_id | period_id | completed_on
----------------------------------------
3 | 3 | 1 | 2010-01-13
4 | 1 | 2 | 2011-01-01
6 | 2 | 3 | 2012-01-13


Both of the below queries always resulted with the first user_id occurance being selected, not the latest (because the ordering happens after the rows are selected from what I understand):

SELECT
DISTINCT user_id,
period_id,
completed_on
FROM my_table
ORDER BY
user_id ASC,
period_id DESC

SELECT *
FROM my_table
GROUP BY user_id
ORDER BY
user_id ASC,
period_id DESC

Answer

Seems like this should work using MAX and a subquery:

SELECT t.Id, t.User_Id, t.Period_Id, t.Completed_On
FROM my_table t
   JOIN (SELECT Max(completed_on) Max_Completed_On, t.User_Id
         FROM my_table
         GROUP BY t.User_ID
         ) t2 ON
      t.User_Id = t2.User_Id AND t.Completed_On = t2.Max_Completed_On

However, if you potentially have multiple records where the completed_on date is the same per user, then this could return multiple records. Depending on your needs, potentially adding a MAX(Id) in your subquery and joining on that would work.