CODEWITHSUNDEEP
sql
anr1234
anr1234

Reputation: 105

sql "group by" to group rows corresponding to same pair

I have a table with 2 columns - "primary" and "secondary". In addition there are other columns too. Following is a sample set of rows:

id=1, primary=A, secondary=B, .....
id=2, primary=C, secondary=D, .....
....
.......
id=200, primary=B, secondary=A, .....
id=300, primary=J, secondary=D, .....

I need a "GROUP BY" query that will group all rows for which the primary and secondary values belong to same pair of values regardless of the order. So, groups should look as follows:

group=1, nodepair=BA, ....   // (primary=A && secondary=B) OR (primary=B && secondary=A)
group=2, nodepair=JM, ....   // (primary=J && secondary=M) OR (primary=M && secondary=J)

Thanks.

Upvotes: 2

Views: 1162

Answers (2)

Ronnis
Ronnis

Reputation: 12843

Are you looking for something like this?

select least(primary, secondary)
      ,greatest(primary, secondary)
 from yourtable
group 
   by least(primary, secondary)
     ,greatest(primary, secondary);

It will give you one group per unique combination of Primary/Secondary regardless of the order, i.e {A,B} is the same as {B,A}.

Upvotes: 2

Polina
Polina

Reputation: 36

select * from table t1
inner join table t2
on t1.primary = t2.secondary 
and t1.secondary = t2.primary

Then you'll have in result:

id=1, primary=A, secondary=B, id=200, primary=B, secondary=A id=200, primary=B, secondary=A, id=1, primary=A, secondary=B

Upvotes: 0

Related Questions