使用MAX聚合函数按最新published_at提取字段的疑问及报错咨询

1. 原SQL报错的原因 #

你遇到的报错是PostgreSQL的严格GROUP BY规则导致的：当使用GROUP BY分组时，SELECT里的列要么是GROUP BY指定的分组列（比如这里的id），要么必须用聚合函数（比如MAX、MIN）处理。因为同一个id下有多条不同的field值，数据库无法确定你要选哪一条，所以直接抛出错误。

2. MAX聚合与对应字段的关联问题 #

如果在宽松模式的数据库中（比如MySQL关闭ONLY_FULL_GROUP_BY参数），你的原SQL可能不会报错，但返回的field值不会自动关联MAX(published_at)对应的那条记录，大概率是随机取分组内的某一条（比如表中第一条），这完全不符合你的需求。聚合函数只负责计算分组内的统计值，不会自动匹配其他字段的对应行。

3. 字符串列能否使用MAX函数？ #

可以用，但MAX对字符串是按字典序进行比较的，比如document3会比document1大，但这和你要的「最新日期对应的field」没有直接关联——如果哪天出现document10，它的字典序比document3大，但日期可能更早，这时候用MAX(field)就会得到错误结果，所以这个方法不适合你的需求。

4. 正确获取最新日期对应记录的方法 #

方法一：窗口函数（通用所有支持窗口函数的数据库） #

用ROW_NUMBER()给每个id下的记录按published_at降序排序，取行号为1的那条：

SELECT id, field, published_at
FROM (
    SELECT 
        id, 
        field, 
        published_at,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY published_at DESC) AS rn
    FROM t1
) AS sub
WHERE rn = 1;

如果同一个id下有多个相同的最大published_at记录，ROW_NUMBER()会随机选一条；如果想保留所有相同最大日期的记录，把ROW_NUMBER()换成RANK()即可。

方法二：子查询关联（兼容老版本数据库） #

先找到每个id的最大published_at，再关联原表获取对应字段：

SELECT t1.id, t1.field, t1.published_at
FROM t1
JOIN (
    SELECT id, MAX(published_at) AS max_pub_at
    FROM t1
    GROUP BY id
) AS sub ON t1.id = sub.id AND t1.published_at = sub.max_pub_at;

这个方法会返回所有id下等于最大published_at的记录（如果存在多条的话）。

方法三：PostgreSQL特有语法DISTINCT ON #

PostgreSQL支持DISTINCT ON语法，可以直接按id去重，保留每组内published_at最大的那条：

SELECT DISTINCT ON (id) id, field, published_at
FROM t1
ORDER BY id, published_at DESC;

注意：ORDER BY必须先写分组字段id，再写排序字段published_at DESC，这样才能保证取到每组内的最新记录。

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