28. October 2014
xhinker
SqlServer
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.