MySQL：子查询SELECT与GROUP BY结合后无法获取最新时间戳

我写了这段SQL查询：

SELECT * FROM (
      SELECT 
      messages.* 
    FROM 
      messages
    ORDER BY 
      messages.created_at DESC
) as messages GROUP BY messages.contact_id

但返回的时间戳不是预期的最新值，反而拿到了旧的记录：

执行前的原始数据：

| id | contact_id | message | created_at          |
|  1 | 4          | ....    | 2023-03-10 19:48:06 |
|  2 | 3          | ....    | 2023-03-10 19:48:06 |
|  3 | 3          | ....    | 2023-03-11 19:48:06 |
|  4 | 4          | ....    | 2023-03-11 19:48:06 |

执行后的结果：

| id | contact_id | message |  created_at          |
|  2 | 3          | ....    |  2023-03-10 19:48:06 |
|  1 | 4          | ....    |  2023-03-10 19:48:06 |

为啥会这样？

原因分析 #

这是MySQL的非严格GROUP BY行为导致的：

• 当你用GROUP BY contact_id时，MySQL会把相同contact_id的行归为一组，但如果你SELECT的是*这种非聚合列（比如id、message、created_at），在默认非严格模式下，MySQL会随机选取分组内的某一行返回，不会参考子查询里的ORDER BY排序结果。
• 子查询里的ORDER BY在这里根本不生效，因为GROUP BY会重新处理数据集合，完全忽略子查询的排序顺序。

正确解法 #

方法1：用窗口函数（MySQL 8.0+支持） #

这是最直观的写法，给每个contact_id分组的行按创建时间倒序排号，取排第一的最新记录：

SELECT id, contact_id, message, created_at
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY contact_id ORDER BY created_at DESC) AS rn
    FROM messages
) t
WHERE rn = 1;

方法2：关联查询（兼容MySQL 5.x版本） #

先找出每个contact_id的最新创建时间，再关联原表拿到对应完整记录：

SELECT m.*
FROM messages m
INNER JOIN (
    SELECT contact_id, MAX(created_at) AS latest_time
    FROM messages
    GROUP BY contact_id
) t ON m.contact_id = t.contact_id AND m.created_at = t.latest_time;

内容的提问来源于stack exchange，提问作者yeln