Geek Answers Handbook

SQL - removing duplicate pairs

I use SQLite to store a set of undirected edges of the graph using two columns: u and v. For instance:

uv

12

3 2

2 1

3 4

I already went through it with SELECT DISTINCT * FROM edge and deleted all duplicate rows.

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

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

Any ideas how to achieve this? Thank!

+5
3

() , , 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 
    )
+4

:

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

:

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)

, (2, 2), , SELECT DISTINCT.

+3

- testing 9 numbers, so I add 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 linking uniques without repeating

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

More articles:


All Articles