PostgreSQL实现按Name分组 每组首元素按modified_date降序排序

你原有SQL存在两处问题：一是给IN子查询追加as a别名属于语法错误，IN后的子查询不需要定义别名，执行会报错；二是仅按name字段排序，无法实现「相同name的记录聚合为一组，每个分组的首元素按modified_date降序排列」的需求。

可直接运行的修正SQL #

支持窗口函数的数据库（MySQL8.0+、PostgreSQL、SQL Server等） #

用窗口函数性能更好，写法更简洁：

SELECT
    modified_date,
    name
FROM table1
WHERE name IN (
    SELECT name FROM table2 GROUP BY name
)
ORDER BY
    -- 按每个name组的最大modified_date倒序，实现组首元素时间越新的组越靠前
    MAX(modified_date) OVER (PARTITION BY name) DESC,
    -- 按name排序，保证同name记录聚合不打散
    name ASC,
    -- 组内按modified_date倒序，让组内最新记录成为首元素
    modified_date DESC;

不支持窗口函数的旧版本数据库（MySQL5.x等） #

用关联子查询实现相同排序逻辑：

SELECT
    t1.modified_date,
    t1.name
FROM table1 t1
WHERE t1.name IN (
    SELECT name FROM table2 GROUP BY name
)
ORDER BY
    (SELECT MAX(modified_date) FROM table1 t2 WHERE t2.name = t1.name) DESC,
    t1.name ASC,
    t1.modified_date DESC;

排序逻辑说明 #

• 优先级最高的排序规则是按每个name分组下最新的modified_date倒序，确保整体结果里，最近有更新的name组排在前面，满足分组首元素按修改时间降序的要求
• 第二层排序按name字段排序，保证同一个name的所有记录连续排列，不会出现同组分隔的问题
• 第三层排序按组内modified_date倒序，让每个分组里最新修改的记录排在组内第一的位置，即分组首元素

内容的提问来源于stack exchange，提问作者Mohamed Aymen Charrada