Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records, could be done by several ways so this is another way to do the same.
You can refer earlier article by following link.
http://blog.extreme-advice.com/2009/02/28/select-delete-duplicate-records-sql-server/
As this is going to be done by CTE, You can get more information about CTE by following link.
Once you create table given in above link, you can use the below given query for selecting or deleting records.
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
Select * from DeleteDuplicateRecords where RowNum>1
Or
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
DELETE from DeleteDuplicateRecords where RowNum>1
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.