Remove Cross duplicate rows in SQL Server
Hello friends, I got one question from my friend who recently appeared for an interview, that how to remove cross duplicate rows in SQL.? So I was looking for a solution but after many failures I got correct solution. Let you explain the question and solution.
Following is my table:-
Now I want Output like following:
Using following easy and fast query you will get desired output:
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 select Id1,Id2 from( 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)as B where id = 1
You can also achieve this using WITH statement. I also got solution using WITH statement, but that will not as fast as above query.
You guys can try by your own and comment it. I would like to see more response from you.
Thanks 🙂