Odstranění duplicitních řádků

Existuje řada způsobů, jak z tabulky odstranit duplicitní řádky. Jedním z nich je využití analytické funkce spolu s CTE (Common Table Expression).

Pomocí analytické funkce ROW_NUMBER() získáme pro každý řádek číslo, které bude unikátní vždy v rámci dané partition, kterou v příkladu níže vytvoříme tak, že pomocí klauzule PARITION BY rozdělíme řádky do skupin podle hodnot ve sloupci Value1. V každé takto vytvořené skupině budou potom řádky očíslovány vzestupě od jedné.

Z takto vytvořené virtuální tabulky poté vybereme pouze řádky, které jsou první v každé skupině. Všechny ostatní a tedy duplicitní záznamy budou z výsledkové sady vyjmuty.

CREATE TABLE dbo.SampleTable (
    Id INT,
    Value1 VARCHAR(10)
)
GO    

INSERT INTO dbo.SampleTable
    SELECT 1, 'Val1' UNION ALL
    SELECT 2, 'Val2' UNION ALL
    SELECT 3, 'Val1' UNION ALL
    SELECT 4, 'Val4'
GO

SELECT * FROM dbo.SampleTable
GO

;WITH cte AS
(
    SELECT
        *, ROW_NUMBER() OVER (PARTITION BY Value1 ORDER BY Id) RN
    FROM dbo.SampleTable
)
DELETE FROM cte
WHERE RN > 1
GO

SELECT * FROM dbo.SampleTable
GO

V prvním případě jsme odstraňovali duplicitní řádky z tabulky, která neměla žádný primární klíč. Nyní si ukážeme, jak odstranit řádky z tabulky, která má primární klíč s IDENTITY(1,1). Můžeme použít metodu podobnou prvnímu příkladu (CTE)  nebo jednoduchý dotaz s vnitřním dotazem ve WHERE podmínce.

-- create SampleTable
CREATE TABLE dbo.tblSampleTable
(
    Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ObjectId INT NOT NULL,
    ObjectName SYSNAME NOT NULL
)
GO

-- populate sample data
INSERT INTO dbo.tblSampleTable
        ( ObjectId, ObjectName )
    SELECT TOP(1000000) c1.object_id, c1.name
    FROM master.sys.columns c1
        CROSS JOIN master.sys.columns c2
        CROSS JOIN master.sys.columns c3
GO    

-- version 1
;WITH Dupl as
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ObjectId, ObjectName ORDER BY (SELECT NULL)) as RN 
    from dbo.tblSampleTable
)
DELETE FROM dbo.tblSampleTable
WHERE Id NOT IN (SELECT Id FROM Dupl WHERE RN = 1)
GO

-- version 2
DELETE FROM dbo.tblSampleTable
WHERE Id NOT IN 
(
    SELECT MIN(Id) Id
    FROM dbo.tblSampleTable
    GROUP BY ObjectId, ObjectName
)
GO