Delete duplicate rows with different values in columns

just 0
Viewed 27

I didn't find my case on the Internet. Tell me how i can delete duplicates if the values are in different columns.

I have a table with a lot of values, for example:

|Id1|Id2|
|89417980|89417978|
|89417980|89417979|
|89417978|89417980|
|89417979|89417980|

I need to exclude duplicates and leave in the answer only:

|Id1|Id2|
|89417980|89417978|
|89417980|89417979|

min/max does not work here, as the values may be different. I tried to union/join tables on a table/exclude results with temporary tables, but in the end I come to the beginning.

tsql
1 Answers

Assuming id1 and id2 are primary keys columns you could try this

DECLARE @tbl table (id1 int, id2 int )
INSERT INTO @tbl
SELECT 89417980, 89417978
UNION SELECT 89417980, 89417979
UNION SELECT 89417978, 89417980
UNION SELECT 89417979, 89417980

SELECT * FROM @tbl

;WITH CTE AS (--Get comparable value as "cs"
  SELECT 
      IIF(id1 > id2, CHECKSUM(id1, id2), CHECKSUM(id2,id1)) as cs
    , id1
    , id2
    , ROW_NUMBER() OVER (order by id1, id2) as rn
  FROM @tbl    
)
, CTE2 AS ( --Get rows to keep
  SELECT MAX (rn) as rn
  FROM CTE
  GROUP BY cs
  HAVING COUNT(*) > 1
)
DELETE tbl -- Delete all except the rows to keep
FROM @tbl tbl
WHERE NOT EXISTS(SELECT 1
  FROM CTE2
  JOIN CTE ON CTE.rn = CTE2.rn
  WHERE CTE.id1 = tbl.id1
  AND   CTE.id2 = tbl.id2
)

SELECT * FROM @tbl
Soerman0
Related