如何检索按日期SQL排序的分组消息 [英] How to retrieve grouped messages ordered by date SQL

问题描述

假装我有下面的消息"表

Let's pretend I have the following "messages" table

ID (auto)   Sender     Receiver    Msg        Date (datetime)
1           Dave       John        Hi         01/01/2013
2           John       Dave        Hello      02/01/2013
3           James      Dave        U there?   02/02/2013
4           Dave       James       Yup        02/03/2013
5           Dave       Simon       Hey        02/03/2013

我希望按日期排列Dave与之交谈的人员列表.

I want the list of people Dave is talking to ordered by Date.

Simon
James
John

我陷入了如何将它们分组而在一个查询中不包含戴夫"的问题.

I'm getting stuck on how to Group them without including "Dave" in one query.

非常感谢.

推荐答案

您似乎想要与Dave交谈的人的与众不同的列表:

You seem to want the distinct list of people that Dave is talking to:

select (case when receiver = 'Dave' then Sender else Receiver end)
from messages m
where 'Dave' in (Receiver, Sender)
group by (case when receiver = 'Dave' then Sender else Receiver end)
order max(date) desc;

这篇关于如何检索按日期SQL排序的分组消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！