sadek sadek - 3 years ago 75
MySQL Question

SQL group by with two columns

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 * FROM
messages
WHERE 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 Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download