Tuesday, February 15, 2011

Remove Duplicate Row without PK

[ Just Copy & Paste ]

declare @TBL table(EmpID int)

insert into @TBL(EmpID)
values(90020),(90020),(90021),(90022),(90023),(90022),(90025),(90022)

select * from @TBL order by EmpID

;with TBLCTE(EmpID,Ranking)
AS
(
select
EmpID,
Ranking = DENSE_RANK() over (PARTITION BY EmpID order by newID())
from @TBL
)
delete from TBLCTE where Ranking > 1

select * from @TBL order by EmpID

No comments:

Post a Comment