SQL — Remove duplicate pairs

Question

I'm using an SQLite to store a set of undirected edges of a graph using two columns, u and v. For example:

u v

1 2

3 2

2 1

3 4

I have already been through it with SELECT DISTINCT * FROM edges and removed all duplicate rows.

However, there are still duplicates if we remember these are undirected edges. In the above example, the edge (1,2) appears twice, once as (1,2) and once as (2,1) which are both equivalent.

I wish to remove all such duplicates leaving only one of them, either (1,2) or (2,1) -- it doesn't really matter which.

Any ideas how to achieve this? Thanks!

Answer 1

This will find all the duplicates:

SELECT t1.u, t1.v FROM table t1 INNER JOIN table t2
 ON t1.u = t2.v AND t1.v = t2.u

This will delete the duplicates:

DELETE FROM table t1 WHERE
  EXISTS (SELECT * FROM table t2 WHERE t2.u = t1.v AND t2.v = t1.u AND t1.u > t2.u)

Note that this will not delete duplicates like (2, 2) but I think you got those already with SELECT DISTINCT.

Answer 2

-- testing for 9 numbers so I'm adding 9 numbers to two tables

 declare @num  int
 set @num =1
 while @num<10
 begin 
 insert into t2 values (@num)
 insert into t1 values (@num)
 set @num +=  1 
 end

--then coupling uniques without any repetition

 select t1.u, t2.v
 from t1 cross join t2
 where t1.u>t2.v

Answer 3

If the same pair (reversed) exists take the one where u>v.

SELECT DISTINCT u,v
FROM table t1 
WHERE t1.u > t1.v
    OR NOT EXISTS (
        SELECT * FROM table t2 
            WHERE t2.u = t1.v AND t2.v = t1.u 
    )