Delete Duplicate record but keep the original record

Straight forward situation when you have the duplicates in a table, but you want to keep just one instance of a row, and delete others

lets say the base table is like this

id	name	sex
1	mac	M
1	mac	F
2	manu	F
2	manu	F
2	manu	F
3	chester	M
3	chester	M

Lets call it SOURCE table, lets put a relative instance count by using row count



go
;with cte as
(
 Select id, row_number() over(partition by id order by id) As Rownumber
 ,Row_number() over (order by id) As R2
 ,(select max(id) from SOURCE) AS MaxId
 from SOURCE
)

--now delete the records for which the instance count is greater than 1

Delete cte
where rownumber > 1

that's it.....

Several variances of same may help you, provide new id's to duplicates, and do a lot of other kind of stuff with duplicate data, do as you will................

Courtesy - Ahmad osama @ http:\\sqlcookbook.wordpress.com