How to delete the Duplicate Rows from SQL Server table

Lets see how to delete the duplicate Rows from SQL Server table.

Sample Table:

ID	NAMES	CODE
1	Mangal	101
1	Mangal	101
1	Mangal	101
2	Ricky	102
3	Brian	103
4	shiv	104
4	Shiv	104
5	Kunal	105
5	Kunal	105
6	Kate	106

As you can see in above table, record with Id 1 has come thrice, and ID 4 and 7 has come twice.

Desired Output: after removing duplicates -

ID	NAMES	CODE
1	Mangal	101
2	Ricky	102
3	Brian	103
4	Shiv	104
5	Kunal	105
6	Kate	106

Create And Load Sample Data -

-- Create Sample table CREATE TABLE Temp ( Id INT, Names VARCHAR(100), Code INT ) GO -- Load sample data INSERT INTO Temp SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 1, 'Mangal', 123 UNION ALL SELECT 2, 'Ricky', 134  UNION ALL SELECT 3, 'Brian', 435 UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 4, 'Shiv', 223   UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 5, 'Kunal', 654  UNION ALL SELECT 6, 'Kate', 611 GO

Now first lets see how to find duplicate rows. With SQL Server 2005/2008 and Row_Number function, it has become very easy.


To Find Duplicate rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) SELECT DISTINCT ID, Names, Code FROM Cte WHERE Seq > 1

To Delete the Duplicate Rows :

WITH Cte AS ( SELECT Id, Names, Code, ROW_NUMBER() Over (PARTITION BY Id ORDER BY ID) as Seq FROM TEMP ) DELETE FROM cte WHERE Seq > 1

- Mangal Pardeshi