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
SELECT * FROM
WHERE sender=10 or reciever = 10 GROUP by sender,reciever
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/DDM89222
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;