How to get unique pairs in SQL when there is many to many relationship in data

Question

I have following data. I need to make unique pairs.

element1        element2
E1              C1
E1              C2
E2              C1
E2              C2
E3              C1
E3              C2

How can I use SQL to get pairs as follows:

E1,C1    E2,C2   Discard E3

You need recursive CTEs for this problem. I don't think Sybase supports them. — Gordon Linoff, Mar 15 '19 at 19:29

The Impaler · Accepted Answer · 2019-03-15T19:34:17.430

2

I would do something like:

select distinct a.element1, b.element2
from (
  select element1, dense_rank() over(order by element1) as rank
  from my_table
) a
join (
  select element2, dense_rank() over(order by element2) as rank
  from my_table
) b on a.rank = b.rank

edited Mar 15 '19 at 19:34

answered Mar 15 '19 at 19:12

The Impaler

45,731
9
39
76

Above code does not work. It only gives me E1,C1 and not E2, C2 – user3376169 Mar 15 '19 at 19:23
I'm sorry I can't really test it. I have SAP ASE, but not SAP ASA. – The Impaler Mar 15 '19 at 19:28
If you run the inner queries separately, do you get `rank`: (1, 2, 3) for the first one, and (1, 2) for the second one? – The Impaler Mar 15 '19 at 19:30

How to get unique pairs in SQL when there is many to many relationship in data

1 Answers1