Tuesday, April 8, 2008

Using CTE and Row_Number() to get the duplicate data in SQL Server 2005

This is the script to get the duplicate data from employee table, whose employee name is repeating.

With TestData as




Row_Number() Over( Partition By EmployeeName Order By EmployeeName) AS Rowid




Select EmployeeId from TestData Where RowId > 1

If we need to get the duplicate records by two columns, those two columns should be in the Partition By column, and in the order by column.

No comments: