Deleting duplicates is one of the interesting problem.
First step of the solution lies in focusing how we consider two records as duplicates?
Then marking the duplicates which followed by deletion.
So to better understand the solution, we consider the following example.
Say we have a table ContactNumberInfo which stores all the contact informations of many customers(CustomerFK) of many contact types(ContactTypeCD) like fax/mobile/landline
Say we have flag isActive to denote whether its is active or not. As per business rule..we can have only one active ContactNumberInfo per customer per ContatType.
Becoz of some reasons, db has some junk which violated the above rule(so duplicate active contacts).
Inorder to delete duplicate junk data…
The idea is to delete all active ContactNumberInfo except recent one per customer per ContactType.
with cte as (
select pkey, debtorETyFK,ContentTypeCD,modified,IsActive ,
row_number() over (partition by debtorETyFK,ContentTypeCD order by modified desc) as [rn]
from ContactNumberInfo where IsActive=1
delete from cte where [rn] > 1