Sunday, January 12, 2014

How to get/retrieve lastest(most recent) records for each user

This question has been asked like a million times on stackoverflow. I will try to explain it with mysql. Let's say we have a table named comments like below:

table comments(sqlFiddle)

and we wanted to select the rows that are latest comments for each user_id (rows highlighted in green). What do we do?
First, we use MAX() function to grab the lastest timestamp for each user_id by using GROUP BY user_id like the query below (sqlFiddle):

SELECT user_id,MAX(timestamp) as maxtimestamp
FROM comments
GROUP BY user_id 

This will give us the below result:
result of maxtimestamp for each user_id
Now, with this result, we inner join back with our comments table to grab data such as comment field (we'll alias this result(subquery) as `maxtt`. So we'll have a query like below (sqlFiddle):

FROM comments c INNER JOIN
   (SELECT user_id,MAX(timestamp) as maxtimestamp
    FROM comments
    GROUP BY user_id

    )as maxtt
ON c.user_id = maxtt.user_id AND c.timestamp = maxtt.maxtimestamp

 Now we have our end result as rows from comments that are the latest (or most recent) for each user_id like below:
latest records for each user_id