如何按照上次创建的另一个表中值的时间戳从一个表顺序中选择所有值

Question

我有两个表，分别称为conversation和message。

我想选择所有按上次更新的与对话相关的消息的顺序排序的对话。会话表没有updated_at，为了能够获得按上次updated_at排序的所有会话，应该从会话中最后创建的消息中从表消息中扣除。

结果应该基于对话的最后一条消息。

我尝试的SQL没有给我正确的结果，我认为我应该使用连接，但由于我是SQL的新手，无法真正了解

select
*
from
conversation
inner join message on
conversation.id = message.conversation_id
order by
message.updated_at desc;    

表格如下

对话

origin_id[/tr>>>xx2>

id	主题	origin_type	created_at
1	t1	doc1	xx1	2021-04-07 13:23:40
2	t2	文档2	2021-04-07 14:23:40
3	t3	doc3	xx3	2021-04-07 15:23:40

您的数据并不能特别说明问题，因为对话只有一条消息。但是，我认为您希望将所有行放在一起进行对话。这将使用窗口函数来获得每次对话的最长更新时间：

order by max(message.updated_at) over (partition by conversation.id) desc,
conversation.id,
message.updated_at desc

编辑：

如果你只想对话没有消息信息，你可以使用子查询：

select c.*
from conversations c
order by (select max(m.updated_at) from messages m where m.conversation_id = c.id) desc;

您只需使用标量子查询：

select *,
(
select max(m.updated_at) from message m
where m.conversation_id = c.conversation_id
) as last_updated_at
from conversation c
order by last_updated_at desc;