All unique combinations of a column based on another one

user_13051408 0
Viewed 111

I have a table with the following columns:

col1 col2
1     1
1     2
1     1
2     1
3     3
3     2
3     1
3     2
3     4
3     1

I want to get all the combinations for each value of col2 with everything else but itself group by col1, i.e. achieve the following output:

col1 col2 col2_combo
1    1    2
3    3    2
3    3    1
3    3    4
3    2    1
3    2    4
3    1    4

I have tried using self join of the table but I can't get a unique result, i.e. a single line for every two values combined.

postgresql sql
1 Answers

There are duplicates in the table. So, the first step is to select distinct values. Well, and then just join the table to itself.

with pairs as (select distinct col1, col2 from mytable)
select a.col1, a.col2, b.col2
from pairs a
join pairs b on b.col1 = a.col1 and b.col2 > a.col2
order by a.col1, a.col2, b.col2;
user_22707620
Related