聊天应用中ORDER BY DESC+LIMIT无法返回最新消息，调整排序方向可解决

问题背景 #

• 开发聊天应用，需展示侧边栏的最近联系人列表
• 聊天表字段：sender, receiver, msg, date
• 原查询SQL（试图获取唯一(sender, receiver)对的最新消息）：

select * from (
  select distinct on (sender, receiver) *
  from (
    select *
    from chats
    where sender = login or receiver = login
    order by date desc
  ) as Q1
  limit 30
) as Q2
order by date desc

• 异常现象：返回结果未包含最新消息；将最内层的order by date desc改为order by date asc后，结果符合预期
• 疑问：为何修改排序方向就能解决问题？猜测DISTINCT ON是从列表底部筛选

核心原因解释 #

你的猜测方向没错，但表述不准确——PostgreSQL的DISTINCT ON逻辑是保留每个分组（这里是(sender, receiver)对）在当前结果集中的第一条记录，而非从底部筛选。原查询的问题出在两个关键逻辑漏洞：

1. 子查询排序的无效性
   Q1中的order by date desc仅服务于limit 30，用来获取最新的30条消息，但当Q1作为子查询被Q2引用时，Q2并没有指定ORDER BY，因此Q1的排序顺序不会被保留。这意味着Q2的结果集顺序是不确定的，DISTINCT ON (sender, receiver)会随机选择每个分组的一条记录，而非Q1中该分组的最新消息。

2. DISTINCT ON未搭配对应排序
   DISTINCT ON的行为依赖于查询的ORDER BY规则：只有在ORDER BY中先指定分组字段，再指定排序优先级，才能确保每个分组保留你想要的那条记录（比如最新消息）。原查询中Q2没有任何ORDER BY，导致DISTINCT ON的结果完全不可控。

为什么改asc能“解决”问题？ #

这其实是巧合：当你把Q1的排序改为date asc，取的是最旧的30条消息，这些消息大概率来自不同的联系人（不会出现同一联系人多条消息挤入前30的情况）。DISTINCT ON会保留每个联系人的旧消息，外层再按date desc排序后，刚好能展示出这些联系人的最新消息（前提是你的数据量不大，这些联系人的最新消息都在表中）。但这只是权宜之计，逻辑上依然不严谨。

正确的查询写法 #

要稳定获取每个联系人（合并双向会话，比如A→B和B→A视为同一联系人）的最新消息，应该让DISTINCT ON与ORDER BY配合：

SELECT *
FROM (
  SELECT DISTINCT ON (least(sender, receiver), greatest(sender, receiver))
         sender, receiver, msg, date
  FROM chats
  WHERE sender = 'login' OR receiver = 'login'
  -- 先按合并后的联系人分组排序，再按消息时间倒序，确保每个分组保留最新消息
  ORDER BY least(sender, receiver), greatest(sender, receiver), date DESC
) AS recent_contacts
-- 最后按消息时间倒序，取前30个最近联系人
ORDER BY date DESC
LIMIT 30;

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