Finding Duplicates in SQL

I’m trying to find duplicates in a non-traditional scenario:

Sample Table:

id1  | id2
-----+-----
ABC  | DEF
DEF  | ABC

I only want to retain one of these rows, as these ids show a connection to each other. What is the most elegant way to filter out the second row?

Answer

To find the dups, but only retain those where id1 is smaller than id2.

An EXISTS can be used for that.

SELECT *
FROM YourTable t
WHERE EXISTS
(
  SELECT 1
  FROM YourTable t2
  WHERE t2.id1 = t.id2 
    AND t2.id2 = t.id1
    AND t2.id1 > t2.id2
);