CODEWITHSUNDEEP
sqlsql-server
ash
ash

Reputation: 3085

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

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

Upvotes: 1

Views: 74

Answers (2)

Tarun
Tarun

Reputation: 154

select b.ID,   
       a.senderid_final,
       a.receiverid_final,
       b.Text 
from 
(
  select a.receiverid as a_receiverid, 
         a.senderid as a_senderid , 
         b.receiverid as b_receiverid, 
         b.senderid as b_senderid, 
         case when max(a.id) > max (b.id) then a.receiverid else b.receiverid end as receiverid_final,
         case when max(a.id) > max (b.id) then a.senderid else b.senderid end as senderid_final
        from my_tables as a
        inner join my_table as b
        on a.receiverid = b.senderid
        and b.receiverid =  a.senderid
        group by a.receiverid, a.senderid, b.receiverid, b.senderid
   ) as a 
   inner join my_tables as b
   on a.receiverid_final = b.receiverid 
   and b.senderid =  a.senderid_final
   Order by b.id desc

Upvotes: 1

ahoxha
ahoxha

Reputation: 1938

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.

Upvotes: 2

Related Questions