Retrieve single from pairs

Question

This is a SQL query question.

If you have a table like this:

ID1         ID2
1709    1689
1689    1709
1934    1501
1501    1934

And you want to retrieve like this:

ID1         ID2
1709    1689
1934    1501

How would you do that? Please note that (1709, 1689) and (1689, 1709) are similar pairs just the IDs being shifted. My purpose is to get a single tuple out of these similar tuples.

score 6 · Accepted Answer · answered Oct 29 '11 at 01:50

Try this, which should work in any dialect (you don't specify what product you're using) that supports CASE:

 SELECT DISTINCT
    CASE WHEN ID1 < ID2 THEN ID1 ELSE ID2 END AS ID1,
    CASE WHEN ID1 < ID2 THEN ID2 ELSE ID1 END AS ID2
    FROM Table

This converts all the rows to have the lower ID first, then uses DISTINCT to remove duplicates.

Retrieve single from pairs

1 Answers1