Sandeep  B J Sandeep B J - 2 years ago 67
MySQL Question

How to get a unique combination of two columns?

I have table like below

member follower
A B
B C
C D
E A
B A
B E
D E


In this above data A - B, B - A having same relationship.. i need unique row either A - B OR B - A

Output should be like below

member follower
A B
B C
C D
E A
B E
D E


I have solved but i just wanted to know whether my logic proper or not.

Answer Source

One possible way is this :

SELECT CASE WHEN t.member > t.follower , t.member , t.follower END as member,
       CASE WHEN t.member > t.follower , t.follower , t.member END as follower
FROM YourTable t
GROUP BY 
       CASE WHEN t.member > t.follower , t.member , t.follower END ,
       CASE WHEN t.member > t.follower , t.follower , t.member END 

Or a more pretty version of this:

 SELECT greatest(t.member,t.follower) as member1,
        least(t.member,t.follower) as member2
FROM YourTable t
GROUP BY 
       greatest(t.member,t.follower) ,
       least(t.member,t.follower) 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download