A friend of mine asked me a very interesting question today that how to delete cross duplicate rows from a table? He had basically two columns named ID1 and ID2 in a table, there he had to find the cross duplicate rows of each combinations and then delete it by keeping only one combination. Initially I thought it is very simple and can be achieved easily but when I tried I got to know the tricky part. Anyway I did it after doing a lot of permutations combinations for 20 Min.
IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #TEMP GO CREATE TABLE #TEMP (ID1 INT, ID2 INT) GO INSERT INTO #TEMP VALUES (2,1) , (2,1) , (1,2) , (3,1) , (5,2) , (2,5) , (4,3) , (3,4) , (6,2) , (2,6) , (2,7) SELECT * FROM #TEMP A --Before ;WITH CTE AS (SELECT ROW_NUMBER() OVER (PARTITION BY (A.ID1 * A.ID2) * (A.ID1 + A.ID2) ORDER BY A.ID1,A.ID2) AS ID, * FROM #TEMP A WHERE EXISTS (SELECT 1 FROM #TEMP B WHERE A.ID1 = B.ID2 AND A.ID2 = B.ID1 )) --SELECT * FROM CTE DELETE FROM CTE WHERE ID > 1 SELECT * FROM #TEMP --After