英文:
Select all conversations with last sent message
问题
我有以下的表格
创建表格 messages(
id 大连续主键,
创建时间 时间戳 包含时区,
消息 文本,
发送者ID 大整数,
接收者ID 大整数
);
我正在使用以下查询选择所有唯一的对话
SELECT DISTINCT ON (LEAST(发送者ID, 接收者ID), GREATEST(发送者ID, 接收者ID))
id, 消息, 创建时间
FROM messages
ORDER BY LEAST(发送者ID, 接收者ID), GREATEST(发送者ID, 接收者ID), 创建时间 DESC;
但我想要更改它，以便我获取每个对话的最后一条消息。而不是第一条。有什么办法可以做到这一点吗？可能使用不同的查询吗？
英文:
I have the following table
create table messages(
id bigserial primary key,
created_at timestamp with time zone,
message text,
from_id bigint,
to_id bigint
);
I'm select all unique conversations by using the following query
SELECT DISTINCT ON (LEAST(from_id, to_id), GREATEST(from_id, to_id))
id, message, created_at
FROM messages
ORDER BY LEAST(from_id, to_id), GREATEST(from_id, to_id), created_at DESC;
But I'd like to change it so I get the last message from each conversation. Not the first. Any idea how I can do that? Possible using a different query?
答案1
得分: 0
如果created_at被定义为NOT NULL（而你只是没有显示它），那么你的查询已经实现了你所要求的功能。
请参考：
- https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
- https://stackoverflow.com/questions/9510509/sort-by-column-asc-but-null-values-first/9511492#9511492
如果每对之间有很多消息，那么不同的查询方式会表现得（更）好。
对于很少的情况，使用DISTINCT ON就可以了。
英文:
If created_at is defined NOT NULL (and you are just not showing it), then your query already does what you ask for.
See:
- https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564
- https://stackoverflow.com/questions/9510509/sort-by-column-asc-but-null-values-first/9511492#9511492
If there are many messages per pair, then different query styles will perform (much) better.
For only few, DISTINCT ON is just right.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论