sql server - How to find the first message between 2 parties in SQL? -

i have messages table looks this:

|   id  | sender_id |  recipient_id | |-------------------|---------------| ... |   1   |    23     |       20      |   |   2   |    11     |       5       | ... |   3   |    20     |       23      | |   4   |    23     |       20      | ... |   5   |    7      |       11      | 

i'm hoping find first message between 2 user ids (the ids in sender_id , recipient_id columns). result above sample be:

|   id  | sender_id |  recipient_id | |-------------------|---------------| ... |   1   |    23     |       20      |   |   2   |    11     |       5       | ... |   5   |    7      |       11      | 

at first thought group checksum of sender_id , recipient_id, , take min message id (id), because checksum different depending upon order of inputs, returns both first message (the intro) , first reply. there alternative checksum in order of inputs irrelevant?

or maybe there's better way arrive @ solution.

any appreciated.

you can use row_number:

online demo

with cte as(     select *,         row_number() over(             partition                  case when sender_id < recipient_id sender_id else recipient_id end,                 case when sender_id > recipient_id sender_id else recipient_id end             order id         ) rn     messages ) select     id, sender_id, recipient_id cte  rn = 1 order id 

you need partition smaller id , greater 1 using case expression.