CODEWITHSUNDEEP
sqlselect
a.dam
a.dam

Reputation: 13

SQL - how to generate distinct pairs from a single column

Could anyone help with how to take a single column:

ID
1
2
3
4

and show all possible distinct pairings in 2 columns:

ID ID
1  2
1  3
1  4
2  3
2  4
3  4

Upvotes: 1

Views: 297

Answers (3)

Rhythm
Rhythm

Reputation: 682

A Simple Cartesian product should do

select distinct a.id as id_a, b.id as id_b
from test a, test b
where a.id<b.id;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

do self join

select t.id,t2.id from t t1 join t t2 on t.id<t1.id

Upvotes: 1

Mureinik
Mureinik

Reputation: 311498

You can join the column on itself and arbitrarily decide that the left hand side will always be smaller than the right hand side (as the example shows):

SELECT t1.col, t2.col
FROM   mytable t1
JOIN   mytable t2 ON t1.col < t2.col

Upvotes: 3

Related Questions