解决PostgreSQL GROUP BY报错：获取指定用户最新会话消息

现有表结构 #

messages表 #

users表 #

需求 #

获取接收者为x的会话数据，每个发送者仅保留按date降序排序后的最新一条消息，期望结果为ID为2和3的两条记录。

原SQL及报错 #

执行以下SQL时出现报错：Column m.id must appear in the GROUP BY clause or be used in an aggregate

select
    m.id,
    u.id as from_id,
    u.username as from_username,
    m.message,
    m.date
from messages as m
join users as u on u.id = m.owner
where m.to = x
group by m.owner
order by m.date desc    

错误原因 #

PostgreSQL遵循严格的SQL标准：当使用GROUP BY时，SELECT子句中的列要么出现在GROUP BY分组字段中，要么被聚合函数（如MAX()、MIN()）包裹。原SQL中m.id、m.message等列既不在GROUP BY里，也未使用聚合函数，因此触发报错。同时直接按m.owner分组的逻辑，无法直接筛选出每个发送者的最新消息，不符合需求。

解决方案 #

使用窗口函数ROW_NUMBER()对每个发送者的消息按日期降序排序，筛选出每个分组内排序为1的记录（即最新消息）：

WITH ranked_messages AS (
    SELECT 
        m.id,
        u.id AS from_id,
        u.username AS from_username,
        m.message,
        m.date,
        ROW_NUMBER() OVER (PARTITION BY m.owner ORDER BY m.date DESC) AS rn
    FROM messages m
    JOIN users u ON u.id = m.owner
    WHERE m.to = 'x'
)
SELECT id, from_id, from_username, message, date
FROM ranked_messages
WHERE rn = 1
ORDER BY date DESC;

执行结果 #

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