如何使用GROUP BY获取每个组中的最新记录?

2021-11-20 00:00:00 sql mysql greatest-n-per-group

假设我有一个名为 messages 的表，其中包含列:

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

id | from_id | to_id | subject | message | timestamp

我只想从每个用户那里获取最新消息，就像您在深入了解实际线程之前在 Facebook 收件箱中看到的一样.

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.

我无法弄清楚这一点.

推荐答案

你应该找出每组(子查询)中最后一个timestamp的值，然后把这个子查询加入到表中 -

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;

相关文章