[T-Sql]Remove duplicated rows

A table named "HistoryTable" stored duplicated history records:

ThreadID

Date

Other columns

111-111

2014-10-27

111-111

2014-10-28

111-111

2014-10-28

222-222

2014-10-27

 

222-222

2014-10-28

222-222

2014-10-28

 

To remove duplicated rows.

delete x
from (
select *, rn=row_number() over (partition by ThreadId,[Date] order by [Date])
from [ThreadHistory]
) x
where rn > 1;

partition by clause divide rows into small groups. And row_number() will count rows within the divided group.

blog comments powered by Disqus