Select the latest message thread values from a table using sql

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

​x
 
Id         |   ReceiverId  |   SenderId   | Text-----------+---------------+--------------+-----------1          |   5           |   1          | text2          |   5           |   1          | text3          |   1           |   5          | text4          |   2           |   5          | text5          |   2           |   5          | text6          |   5           |   3          | text7          |   5           |   4          | text9          |   5           |   6          | text10         |   5           |   4          | text11         |   10          |   5          | text12         |   5           |   10         | text13         |   10          |   5          | text14         |   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

 
Id         |   ReceiverId  |   SenderId   | Text-----------+---------------+--------------+-----------14         |   5           |   10         | text10         |   5           |   4          | text9          |   5           |   6          | text6          |   5           |   3          | text5          |   2           |   5          | text3          |   1           |   5          | text​

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

 
select Id, ReceiverId, SenderId, [Text]from MyTable twhere 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.

Advertisement

Answer