SQL SERVER: Find Duplicate Rows with Row_Number

This is very known question to find Duplicate Rows from the Table and Delete Duplicate rows from the table

I came to know about “ROW_NUMBER()” of SQL SERVER 2005.

Lets see how we can use this feature:

There is one Table:

CREATE TABLE FindDuplicates(
EmpID INT IDENTITY(1,1),
EmpName VARCHAR(500)
)

INSERT some data into the table:

INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('B')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('D')
INSERT INTO FindDuplicates VALUES('D')

Now we have data like:

Now, we need to Find the duplicate Rows from the table. So we need to get “A”, “C” and “D”.

Lets see the use of Row_Number():

;With CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpName) As RowID,
EmpID,
EmpName
FROM FindDuplicates
)
SELECT *
FROM CTE
WHERE RowID > 1

Output will be like:

So we can Delete Duplicate Rows as we have EmpID (Primary Key)

I Used:

PARTITION BY: which will find same rows and assign ID accordingly.

ORDER BY: In which order we want to assign ID. By Default its Ascending

About these ads