sql - SQL Server 使用分区删除重复项

Question

我需要从表中删除重复项:

;WITH cte as(
SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
                                       ORDER BY ( SELECT 0 ) ) RN
         FROM   quicklabdump)
        delete from cte where RN>1

quicklabdumpID 列是主键。

我想知道如何在多次出现[specimen id]的情况下仅保留最大的quicklabdumpID

Answer 1

将订单更改为 quicklabdumpid DESC。

WITH cte as(
  SELECT ROW_NUMBER() OVER (PARTITION BY [specimen id]
                            ORDER BY  quicklabdumpid DESC ) RN
  FROM   quicklabdump)
delete from cte where RN>1