SQL group by with two columns

Question

I am facing a very interesting problem. Hope you have the answer.

First see my table structures

id  | sender |  receiver | msg
1   |   10   |    5      | hello
2       10        5         df
3       5        10         fds
4       10        7         sdf

Now I want to select only distinct pairs from sender and receiver (Not 5,10 or 10, 5 because both are same)

I was using SELECT * FROMmessagesWHERE sender=10 or reciever = 10 GROUP by sender,reciever But this returns 10, 5 and 5,10 but I want only one. Please help

Answer 1

mySQL doesn't have analytical functions which would make this pretty straight forward; but we can simulate them. I could use a row_number over (partition by the user1, user2) but in mysql we use 3 user variables to simulate this.

The inner most query (alias A) defines two new columns user1 and user2. It ensures that user1 is always less than user2 giving me a way to group the inverted pairs ((10,5),(5,10))

The results of this query are then assigned a row number to each sender receiver group using user1 and user2 generated in the subquery (alias B).

Finally the results of the 2nd subuqery are limited to show only the first row and return the correct ordered pair of sender receiver.

This breaks down the query and shows the results of each subquery helping explain how this works: http://rextester.com/CTD16984

Which gives us a SQL statement of the below.

SELECT ID, Sender, Receiver
FROM (SELECT A.*
           , Case when user1 = @user1 and user2 = @user2 then @RN:=@RN+1 else @RN:=1 end as RN
           , case when user1 = @User1 then user1 else @user1:=user1 end u1
           , case when user2 = @User2 then user2 else @user2:=user2 end u2
      FROM (SELECT ID, sender, receiver
                 , case when Sender < Receiver then Sender else Receiver end user1
                 , case when Sender > Receiver then Sender else Receiver end user2
            FROM SQLFOo) A
      cross join (Select @RN:=0, @User1:='', @user2:='') b
      ORDER BY U1, U2, ID
      ) C
WHERE RN = 1
#based on last comment is this what you mean?
# AND (Sender = 10 or receiver = 10); 

Answer 2

How about this...

SELECT * FROM messages WHERE receiver NOT IN  
(SELECT sender FROM messages WHERE sender = 10 or receiver = 10 GROUP by sender)   
AND (sender = 10 OR receiver = 10);

This should take all of your pairs in the inner query and then deducts that the only way you'll have a collision is if sender = receiver and receiver = sender, so it removes matches where sender = receiver.