Delete duplicates with out cte?

Question

My friend asked me the follwing question. _______________________________________ Employee Id Name 1 A 2 B 1 A 5 V 2 B _____________________________________________________ Delete duplicate rows from the table with out using CTE?

Shahriar Nikkhah · Answer

DECLARE @tblXYZ AS TABLE ( [id] Int , [ Name ] nVarChar(10) ) INSERT INTO @tblXYZ VALUES (1, 'A' ) , (2, 'B' ) , (5, 'V' ) , (1, 'A' ) , (2, 'B' ) --- For Test --SELECT MT.* FROM @tblXYZ MT --- For Test, for the deletion, just fing the duplicate --SELECT MT.[id] , COUNT(*) --FROM @tblXYZ MT --GROUP BY MT.[id] --HAVING COUNT(*) > 1 DELETE MT FROM @tblXYZ MT INNER JOIN ( SELECT [id] , COUNT (*) AS idRowCount FROM @tblXYZ GROUP BY [id] HAVING COUNT (*) > 1 ) ST ON MT.[id] = ST.[id] SELECT MT.* FROM @tblXYZ MT

Shahriar Nikkhah · Answer

I have provided you 2 solutions good luck

Shahriar Nikkhah · Answer

Use this code (it's without CTE) DECLARE @tblXYZ AS TABLE ( [id] Int , [ Name ] nVarChar(10) ) INSERT INTO @tblXYZ VALUES (1, 'A' ) , (2, 'B' ) , (5, 'V' ) , (1, 'A' ) , (2, 'B' ) --- For Test --SELECT MT.* FROM @tblXYZ MT --- For Test, for the deletion, just fing the duplicate --SELECT [RowNumber] = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [name] ASC ) -- , MT.* --FROM @tblXYZ MT DELETE MT FROM ( SELECT [RowNumber] = ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [ name ] ASC ) , MT.* FROM @tblXYZ MT ) MT WHERE [RowNumber] > 1 SELECT MT.* FROM @tblXYZ MT

Shahriar Nikkhah · Answer

You may have.... In your senario the answer is no, why? as an example i have to delete the Id = 1 and name = 'A' , my question which one. OR your answer can be YES just use CTE is more easier

Sai Reddy · Answer

Thank you Shahriar Nikkhah Here duplicates were deleted with id=1 Is it possible to keep one value with id=1 and rest should be eleiminated? i mean 1 A 2 B 5 V

Delete duplicates with out cte?

Answers (5)