使用PostgreSQL的MAX()函数后如何获取该行其他关联字段？

问题场景 #

我有一个名为orders的表，包含主键id列、order_id列和created_date列，表数据如下：

id | order_id |    created_date
-----------------------------------
 1 |   178    | 2022-11-16 09:25:11
 2 |   182    | 2022-11-18 08:44:19
 3 |   178    | 2022-11-17 11:16:22
 4 |   178    | 2022-11-18 14:55:41
 5 |   195    | 2022-11-15 09:11:17
 6 |   195    | 2022-11-16 21:22:32
 7 |   146    | 2022-11-16 16:55:09
 8 |   178    | 2022-11-16 04:39:16
 9 |   121    | 2022-11-16 01:20:19

需要获取指定order_id（比如178）对应最大created_date的那一行的id和created_date，期望结果为：4 | 2022-11-18 14:55:41。

之前尝试的查询：

SELECT   MAX(o.created_date),
         o.id
FROM     orders o
WHERE    o.order_id = 178
GROUP BY o.id;

返回了多行数据，移除GROUP BY也无法得到正确结果，需要正确的PostgreSQL查询语句。

解决方案 #

方法1：窗口函数ROW_NUMBER()（推荐，支持批量查询）

这种方法适合需要同时处理多个order_id的场景，逻辑清晰且灵活：

SELECT id, created_date
FROM (
    SELECT 
        id, 
        created_date,
        -- 按order_id分组，每组内按created_date倒序排号
        ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY created_date DESC) AS rn
    FROM orders
    WHERE order_id = 178
) t
-- 取每组的第一条（最新记录）
WHERE rn = 1;

如果要一次性获取所有order_id的最新记录，只需去掉内层查询的WHERE order_id = 178即可。

方法2：子查询关联

先找到指定order_id的最大created_date，再关联原表匹配对应行：

SELECT o.id, o.created_date
FROM orders o
WHERE o.order_id = 178
AND o.created_date = (
    -- 子查询获取当前order_id的最大时间
    SELECT MAX(created_date) 
    FROM orders 
    WHERE order_id = 178
);

如果存在多条记录共享同一个最大created_date，这个查询会返回所有符合条件的行；若只需一行，可在末尾添加LIMIT 1。

方法3：ORDER BY + LIMIT（单order_id场景最简写法）

针对单个order_id的查询，直接排序后取第一条：

SELECT id, created_date
FROM orders
WHERE order_id = 178
-- 按时间倒序，最新的排在最前
ORDER BY created_date DESC
-- 只取第一条
LIMIT 1;

这种写法简单直接，但仅适用于单个order_id的查询，无法批量处理多个分组。

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