删除具有最小日期和多个键的MS-SQL重复

I have a big table that has duplicates as such:

我有一张大桌子，上面有重复的东西:

number (primary key),
编号(主键),
group (primary key),
集团(主键)
Division (primary key),
部门(主键),
dateChange.
dateChange。

Example:

例子:

1,2,3,20121015

1,2,3,20120101

1,2,3,20110101

2,2,2,20121010

2,2,2,20120101

result should be:

结果应该是:

1,2,3,20121015

2,2,2,20121010

I have tried many combinations including group by the primary key with minimum "changeDate" but nothing seems to work perfectly.

我尝试过很多组合，包括组按主键和最小“change edate”，但似乎没有什么是完美的。

I want to have something like this:

我想要这样的东西:

delete from table where (number,group.devision,changeDate) not in 
(select from table(number,group,devision,Max(changeDate)) 
group by (number,group.devision)

But I dont think it is a valid MS-SQL syntax.

但是我不认为这是一个有效的MS-SQL语法。

Your help will be very appreciated!!

非常感谢您的帮助!!

2 个解决方案

#1

To delete all rows except for the latest for a number, group, Division combination.

删除所有行，除了最新的数字、组、分区组合。

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY number, group, Division 
                                       ORDER BY dateChange DESC) RN
         FROM   YourTable)
DELETE FROM cte
WHERE  RN > 1

#2

The following should work.

下面的工作。

delete table from
table inner join (select 
number, group, division, changeDate, row_number() over 
(partition by number, group, division order by changeDate desc) as ranker
from table) Z
on table.number  = Z.number and table.group = Z.group and 
table.changeDate = Z.changeDate and Z.ranker != 1

#1