如何从charges表按profile_id分组取最大failed_at对应行并保留id列

错误原因说明 #

1. 语法报错：你在GROUP BY之后直接在ORDER BY子句引用原表的charges.id，分组后原表的单行id已经不存在，所以触发了必须出现在GROUP BY子句或被聚合函数包裹的报错。
2. 逻辑问题：你原有写法里同时取MAX(id)和MAX(failed_at)是两个独立的聚合结果，无法保证拿到的id就是对应同组最大failed_at的那行数据的id，仅在示例数据场景下刚好符合预期，逻辑不通用。

正确实现方案 #

方案1：窗口函数法（兼容MySQL8+、PostgreSQL、SQL Server等所有支持标准SQL的数据库）

SELECT id, profile_id, failed_at
FROM (
    SELECT 
        id,
        profile_id,
        failed_at,
        -- 按profile_id分组，组内按failed_at倒序排序，排名第一的就是每组最大failed_at的行
        ROW_NUMBER() OVER (PARTITION BY profile_id ORDER BY failed_at DESC) AS rank_num
    FROM charges
) temp
WHERE rank_num = 1
ORDER BY id ASC;

方案2：子查询关联法（兼容所有数据库，包括MySQL5.x等低版本）

SELECT c.id, c.profile_id, c.failed_at
FROM charges c
INNER JOIN (
    -- 先查询每个profile_id对应的最大failed_at
    SELECT profile_id, MAX(failed_at) AS max_failed_at
    FROM charges
    GROUP BY profile_id
) t ON c.profile_id = t.profile_id AND c.failed_at = t.max_failed_at
ORDER BY c.id ASC;

方案3：PostgreSQL专属极简写法

SELECT id, profile_id, failed_at
FROM (
    SELECT DISTINCT ON (profile_id) id, profile_id, failed_at
    FROM charges
    ORDER BY profile_id, failed_at DESC
) temp
ORDER BY id ASC;

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