SQL -- Remove duplicate pairs

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!

---

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 
    )

---

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.

---

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