This is my table
Id | ReceiverId | SenderId | Text -----------+---------------+--------------+----------- 1 | 5 | 1 | text 2 | 5 | 1 | text 3 | 1 | 5 | text 4 | 2 | 5 | text 5 | 2 | 5 | text 6 | 5 | 3 | text 7 | 5 | 4 | text 9 | 5 | 6 | text 10 | 5 | 4 | text 11 | 10 | 5 | text 12 | 5 | 10 | text 13 | 10 | 5 | text 14 | 5 | 10 | text
How do I select a row with out duplication based on [ReceiverId, SenderId] pair and Ordered by Id in Descending order. That is: [5, 1]=[1,5] are duplicate. OR [5,1] = [5,1] are also the duplicate.
So the final result should be:
Id | ReceiverId | SenderId | Text -----------+---------------+--------------+----------- 14 | 5 | 10 | text 10 | 5 | 4 | text 9 | 5 | 6 | text 6 | 5 | 3 | text 5 | 2 | 5 | text 3 | 1 | 5 | text
Advertisement
Answer
Assuming that among records, which you consider to be the same by just checking the SenderId and ReceiverId (order doesn’t matter), you want the one with the largest Id (which could probably be the latest). Then, this query will give you the result:
select Id, ReceiverId, SenderId, [Text]
from MyTable t
where t.Id in (
select top 1 tt.Id
from MyTable tt
where (tt.SenderId = t.SenderId and tt.ReceiverId = t.ReceiverId) or
(tt.SenderId = t.ReceiverId and tt.ReceiverId = t.SenderId)
order by tt.Id desc
)
order by t.Id desc
Replace MyTable with your table’s name.