Search code examples
mysqlsqlgreatest-n-per-group

How to get the latest record in each group using GROUP BY?

Let's say I have a table called messages with the columns:

id | from_id | to_id | subject | message | timestamp

I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.

This query seems to get me close to the result I need:

SELECT * FROM messages GROUP BY from_id

However the query is giving me the oldest message from each user and not the newest.

I can't figure this one out.

Solution

  • You should find out last timestamp values in each group (subquery), and then join this subquery to the table -

    SELECT t1.* FROM messages t1
  JOIN (SELECT from_id, MAX(timestamp) timestamp FROM messages GROUP BY from_id) t2
    ON t1.from_id = t2.from_id AND t1.timestamp = t2.timestamp;