时间戳

首页 > 解决方案 > 删除 SQL Server 中重复的最大行

sql - 删除 SQL Server 中重复的最大行

问题描述

我想删除 SQL 中的重复行。我的查询可以删除 的重复行RN > 1，但我想删除max(RN)：

WITH CTE AS(
   SELECT [col1], [col2], [col3]
       RN = ROW_NUMBER()OVER(PARTITION BY [col1], [col2] ORDER BY [col1], [col2])
   FROM dbo.Table1
)
DELETE FROM CTE WHERE RN > 1

示例数据

标签： sqlsql-serverduplicatescommon-table-expressiondelete-row

解决方案

只需将分区更改为与当前顺序相反，然后仅删除具有第一行号的记录：

WITH CTE AS (
    SELECT [col1], [col2], [col3]
           RN = ROW_NUMBER() OVER (PARTITION BY [col1], [col2] ORDER BY [col1] DESC, [col2] DESC)
    FROM dbo.Table1
)

DELETE FROM CTE WHERE RN = 1;

推荐阅读