CODEWITHSUNDEEP
sqlduplicatesrows
Ulrik Balslev
Ulrik Balslev

Reputation: 53

"duplicate" rows - how to select distinct

I have a table with this structure

id1     id2
--------------
10     2
2     10
12    15

I need to select "distinct" using SQL in the sense that rows 1 and 2 are considered the same

So I need a query that results in

10    2
12   15

or

2    10
12   15

Both are fine.

Any good ideas. This problem is driving me crazy :-)

Upvotes: 0

Views: 105

Answers (4)

Malta
Malta

Reputation: 544

Another solution, using relations instead of a DISTINCT clause:

SELECT A.id1, A.id2
FROM mytable A LEFT JOIN mytable B ON A.id1 > B.id1 AND A.id1 = B.id2 AND A.id2 = B.id1
WHERE B.id1 IS NULL

Upvotes: 0

Malta
Malta

Reputation: 544

I would do:

SELECT DISTINCT id1, id2 
FROM (
    SELECT id1, id2 FROM mytable
    UNION
    SELECT id2, id1 FROM mytable
) AS combinations

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

In a DBMS that supports LEAST and GREATEST you can use these to get ordered pairs:

select distinct
  least(id1, id2) as lesser_id,
  greatest(id1, id2) as greater_id
from mytable;

In a DBMS that doesn't support these functions , you can use CASE expressions to achieve the same:

select distinct
  case when id1 <= id2 then id1 else id2 as lesser_id,
  case when id1 >= id2 then id1 else id2 as greater_id
from mytable;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

One simple method is:

select t.*
from t
where a < b or
      not exists (select 1 from t t2 where t2.b = t.a and t2.a = t.b)

Upvotes: 2

Related Questions