CODEWITHSUNDEEP
mysql
Sandeep B J
Sandeep B J

Reputation: 53

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.

Upvotes: 2

Views: 94

Answers (3)

Strawberry
Strawberry

Reputation: 33935

This is all the query you need:

SELECT DISTINCT LEAST(member,follower) member_1
             , GREATEST(member,follower) member_2 
          FROM my_table;

The rest is noise.

Upvotes: 1

sagi
sagi

Reputation: 40491

One possible way is 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)

Upvotes: 0

Matt
Matt

Reputation: 15061

Try something like:

SELECT member, follower
FROM yourtable
WHERE (SELECT COUNT(*) FROM yourtable WHERE CONCAT(member, follower) = REVERSE(CONCAT(member, follower))) <= 1

Upvotes: 0

Related Questions