sql - 区分一列，按另一列排序

Question

我正在为应用构建消息传递功能，我的数据库表如下所示:

messages:
  id
  senderId
  receiverId
  threadId   <-- every message is part of a thread
  createdAt

我有一个“消息”页面，每个消息线程都有一张卡片，但我希望它显示卡片中的最新消息作为每个线程的预览。这是我到目前为止编写的查询:

SELECT DISTINCT ON ("threadId") * FROM messages ORDER BY "createdAt" DESC;

所以我正在执行 DISTINCT ON ("threadId")，但我也选择了所有列，因为我需要所有数据，这就是为什么有 * 在 DISTINCT ON 子句之后。此查询在此之前运行良好，但现在它在 ORDER BY 子句上失败了。我想返回上次创建的消息，但出现错误:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions
LINE 1: SELECT DISTINCT ON ("threadId") * FROM messages order by "cr...

我理解我认为的错误(ORDER BY 列必须与应用 DISTINCT ON 的列相匹配)但我不知道如何获取查询是我想要的，它在变得不同之前由 createdAt 排序。

Answer 1

ORDER BY 中的前导表达式必须与 DISTINCT ON 中的表达式一致:

SELECT DISTINCT ON ("threadId") *
FROM   messages
ORDER  BY "threadId", "createdAt" DESC;

详细解释:

• Select first row in each GROUP BY group?

---

如果您想要以不同的方式(如注释)对结果进行排序，您必须使用第二个将查询包装在外部查询中订购方式。见:

• PostgreSQL DISTINCT ON with different ORDER BY

或类似，具体取决于您的具体情况和要求。获得最佳结果可能需要一些技巧。最近的例子:

• How do I take a DISTINCT ON subquery that is ordered by a separate column, and make it fast?