This blog is for SQL SERVER developers. Here I am trying to share my experience with all people. This blog contains my thoughts that might help the people

Menu

March 19, 2009

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

select max(EmpID)from FindDuplicates group by EmpNamehaving count(*)>1while @@rowcount>0 delete from FindDuplicates where EmpID in ( select max(EmpID) from FindDuplicates group by EmpName having count(*)>1 )

I want to display all duplicate records in the table. My query has to fetch all the records which are duplicate(First Name or Last Name). Also I want the ability to also pull names where there might be a middle initial placed in the end of the first name field, (i.e., "Maria Z. " vs. "Maria") as well.