sql - 选择 SQL Server 中每个唯一组合的最后一行

Question

无论组合元素顺序如何，我都需要选择每个唯一组合的最后一行。例如组合(5,6)和(6,5)将被视为相同的组合。

这是表上的数据：

我的预期输出：

到目前为止我做了什么：

Select * From
(
Select *, ROW_NUMBER() over (Partition by SenderId, ReceiverId order by MessageId desc) as Seq From Messages
) as TempTalbe Where Seq = 1

我的查询的输出是：

SQL Server 专家有什么帮助吗？

标签： sqlsql-servertsql

Answer 1

你可以用row_number()这个。传统的方法是：

select m.*
from (select m.*,
             row_number() over (partition by (case when senderId < receiverId then senderId else receiverId end),
                                             (case when senderId < receiverId then receiverId else senderId end)
                                order by messageSentAt desc
                               ) as seqnum
      from messages m
     ) m
where seqnum = 1;

您也可以在没有子查询的情况下对其进行表述：

select top (1) with ties m.*
from messages m
order by row_number() over (partition by (case when senderId < receiverId then senderId else receiverId end),
                                         (case when senderId < receiverId then receiverId else senderId end)
                            order by messageSentAt desc
                           );