sql - 在 Postgresql 中查找最近的不同记录并按 created_at 排序

Question

我认为这很容易找到解决方案，但我找不到与我正在尝试做的事情相匹配的问题。

这是我想看到的(这个查询不起作用):

SELECT DISTINCT ON (conversation_id) 
  *
FROM messages
ORDER BY created_at DESC

换句话说，返回包含 conversation_id 不同的最近 created_at 日期的完整行，然后按 created_at DESC 排序。

我在下面写了一个查询，它完全符合我的要求，但我认为我把它复杂化了。我还假设最近的记录将具有最高 ID 并按子查询中的 ID 排序，而不是“created_at”(它返回与 created_at DESC 相同的顺序，但对读者的友好性较低)。

SELECT 
  m.*
FROM (
  SELECT 
    DISTINCT ON (conversation_id) conversation_id,
    id
  FROM messages
  ORDER BY conversation_id, id DESC
) as t
INNER JOIN messages as m
  ON t.id = m.id
ORDER BY t.id DESC

任何帮助简化此查询的版本都会很棒。

---

添加示例以进行说明

如果消息表如下所示:

id, conversation_id, created_at, subject
1, 2,  "2014-10-21 00:01:00", "subject 1"
2, 43, "2014-10-21 00:02:00", "subject 2"
3, 12, "2014-10-21 00:03:00", "subject 3"
4, 2,  "2014-10-21 00:04:00", "subject 4"
5, 43, "2014-10-21 00:05:00", "subject 5"

查询应返回以下内容:

id, conversation_id, created_at, subject
5, 43, "2014-10-21 00:05:00", "subject 5"
4, 2,  "2014-10-21 00:04:00", "subject 4"
3, 12, "2014-10-21 00:03:00", "subject 3"

Answer 1

不需要连接:

select *
from (
    select distinct on (conversation_id) *
    from messages
    order by conversation_id, id desc
) t
order by id desc