按region_id分组获取最大ID对应行结果错误，求正确SQL实现

问题说明 #

现有Serians表数据如下：

+-------+----------+-----------------------+---------------------+
|  id   | region_id| code_id               |     created_at      |
+-------+----------+-----------------------+---------------------+
| 51015 |     1510 |                     2 | 2023-03-22 11:05:36 |
| 51016 |     1670 |                     2 | 2023-03-22 11:08:52 |
| 51017 |     1670 |                     1 | 2023-03-22 11:08:58 |
| 51018 |     1510 |                     2 | 2023-03-22 11:32:58 |
+-------+----------+-----------------------+---------------------+

需求：按region_id去重，获取每个region_id对应最大id的完整行，预期结果为：

+-------+-----------+---------+---------------------+
|  id   | region_id | code_id |     created_at      |
+-------+-----------+---------+---------------------+
| 51017 |      1670 |       1 | 2023-03-22 11:08:58 |
| 51018 |      1510 |       2 | 2023-03-22 11:32:58 |
+-------+-----------+---------+---------------------+

原执行的SQL存在问题：

SELECT MAX(sa.id) as id, sa.region_id, sa.code_id, MAX(sa.created_at) as created_at
FROM Serians AS sa
WHERE sa.region_id IN (1670,1510)
GROUP BY sa.region_id

错误原因：GROUP BY后，未被聚合的code_id会返回分组内的随机值，并非对应最大id行的字段值，得到的错误结果如下：

+-------+-----------+---------+---------------------+
|  id   | region_id | code_id |     created_at      |
+-------+-----------+---------+---------------------+
| 51017 |      1670 |       2 | 2023-03-22 11:08:58 |
| 51018 |      1510 |       2 | 2023-03-22 11:32:58 |
+-------+-----------+---------+---------------------+

正确解决方案 #

方法1：子查询关联 #

先通过子查询拿到每个region_id对应的最大id，再关联原表获取完整行数据：

SELECT sa.*
FROM Serians sa
INNER JOIN (
    SELECT region_id, MAX(id) AS max_id
    FROM Serians
    WHERE region_id IN (1670,1510)
    GROUP BY region_id
) t ON sa.region_id = t.region_id AND sa.id = t.max_id
WHERE sa.region_id IN (1670,1510)

方法2：窗口函数（ROW_NUMBER()） #

用窗口函数按region_id分组，对id降序排序，取每组的第一行：

SELECT id, region_id, code_id, created_at
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY region_id ORDER BY id DESC) AS rn
    FROM Serians
    WHERE region_id IN (1670,1510)
) t
WHERE rn = 1

这两种方法都能精准获取每个region_id对应最大id的完整行，符合预期结果。

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