求先ORDER BY再GROUP BY的SQL查询：按地址取最短code成员

需求回顾 #

现有people表，字段包括ID(smallint)、code(varchar)、FullName(varchar)、ADDRESS1(varchar)、ADDRESS2(varchar)、ADDRESS3(varchar)、COUNTRY(varchar)、IsMember(smallint)。需要：

• 仅返回IsMember=1的记录
• 每个ADDRESS1对应唯一一条记录
• 该记录为对应地址下code长度最短的成员（即原生家庭最年长者）

正确SQL查询 #

使用窗口函数ROW_NUMBER()实现分组内排序并筛选目标记录，这是解决这类分组取最值问题的标准方案：

SELECT 
    ID,
    code,
    FullName,
    ADDRESS1,
    ADDRESS2,
    ADDRESS3,
    COUNTRY
FROM (
    SELECT 
        ID,
        code,
        FullName,
        ADDRESS1,
        ADDRESS2,
        ADDRESS3,
        COUNTRY,
        -- 按地址分组，组内按code长度升序排序，最短的标记为1
        ROW_NUMBER() OVER (
            PARTITION BY ADDRESS1 
            ORDER BY LENGTH(code) ASC, code ASC
        ) AS rn
    FROM `people`
    WHERE IsMember = 1
) AS ranked_people
-- 保留每个地址组中排名第一的记录
WHERE rn = 1;

方案解释 #

1. 窗口函数ROW_NUMBER()：通过PARTITION BY ADDRESS1将数据按地址分组，ORDER BY LENGTH(code) ASC确保组内按code长度从短到长排序；额外添加code ASC是为了兼容code长度相同的场景，保证结果稳定。
2. 子查询筛选：外层查询仅选取rn=1的记录，即每个地址组中code最短的成员。

为什么你的尝试失败？ #

• 直接先排序再分组：SQL语法不允许GROUP BY前使用ORDER BY，分组与排序的逻辑顺序不符合SQL执行规则。
• 嵌套查询后分组：子查询的ORDER BY在无LIMIT约束时不会被保留，分组时会随机选取组内记录，无法保证取到code最短的成员。

验证示例数据 #

用你提供的示例数据执行上述查询，会得到期望结果：

+----+------+----------+----------+----------+----------+----------+
| ID | code | FullName | ADDRESS1 | ADDRESS2 | ADDRESS3 | COUNTRY  |
+----+------+----------+----------+----------+----------+----------+
| 1  | A1   | Alex     | Main 10  | 1234 AB  | New York | USA      |
| 2  | A1.2 | Fred     | Longstr 1| 5757 GE  | London   | UK       |
| 4  | J2   | John     | 2nd Str 7| 1970 BV  | The Hague| NL       |
+----+------+----------+----------+----------+----------+----------+

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