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
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
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
No comments:
Post a Comment