I am getting duplicate rows how to remove in sql server

Question

Suthahar Jegatheesan · Answer

Hello Nabin, I would prefer CTE for deleting duplicate rows from sql server table, if you want to keep orginal row and remaining whould be deleted. WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY , , ORDER BY , , ) AS RN FROM ) DELETE FROM CTE WHERE RN 1 Run above query with repace your coulumn name and table name Column nmae : , , Table Name :

Mageshwaran R · Answer

DELETE FROM Employee LEFT OUTER JOIN ( SELECT MIN (RowId) as RowId, Col1, Col2, Col3 FROM Employee GROUP BY Col1, Col2, Col3 ) as KeepRows ON Employee .RowId = KeepRows.RowId WHERE KeepRows.RowId IS NULL

Mageshwaran R · Answer

Hi Nabin, Try this query, CREATE TABLE Employee ( [ID] INT identity(1,1), [FirstName] Varchar (100), [LastName] Varchar (100), [Country] Varchar (100), ) GO Insert into Employee ([FirstName],[LastName],[Country] ) values ( 'Raj' , 'Gupta' , 'India' ), ( 'Raj' , 'Gupta' , 'India' ), ( 'Mohan' , 'Kumar' , 'USA' ), ( 'James' , 'Barry' , 'UK' ), ( 'James' , 'Barry' , 'UK' ), ( 'James' , 'Barry' , 'UK' ) SELECT [FirstName], [LastName], [Country], COUNT (*) AS CNT FROM [SampleDB].[dbo].[Employee] GROUP BY [FirstName], [LastName], [Country] HAVING COUNT (*) > 1;

Rajanikant Hawaldar · Answer

use distinct

Nabin Agarwala · Answer

Thank you sir

I am getting duplicate rows how to remove in sql server

Answers (5)