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

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 
    )