Duplikate entfernen

Wer in die Verlegenheit kommt Duplikate aus einer Tabelle zu entfernen, kommt schnell auf die Lösung, die Sätze mit DISTINCT herauszukopieren, die Daten in der Quell-Tabelle zu löschen und die distinkten Sätze zurück zu kopieren. Hier kommt ein ganz anderer Weg mit Window-Functions.

Ausgangslage

Durch welchen Fehler auch immer haben wir unsere Daten gleich dreimal in der Tabelle und natürlich keinen Unique-Key, der das verhindert hätte. Die Spalten Part und Ord sollten in der Kombination eigentlich eindeutig sein. Die anderen Spalten der Tabelle werden hier nicht betrachtet.

Lösung

Wir nutzen die beiden Window-Functions ROW_NUMBER() und RANK(). Die erste nummeriert alle Sätze in der Tabelle durch und die zweite erstellt ein Ranking über diese Sätze. Da wir die gesamte Tabelle betrachten wollen, verwenden wir nicht die Klausel PARTITION BY, sondern nur das ORDER BY.
Gleiche Sätze erhalten den gleichen Rang und beim nächsten wechselnden Schlüssel wird der Rang durch die Anzahl Ränge vorher + 1 ermittelt. Hier erfolgt also ein Sprung zu jeder Gruppe.

Vergleicht man jetzt die Werte von ROW_NUMBER() und RANK(), so stellt man fest, dass immer beim ersten Satz einer Gruppe diese beiden Werte übereinstimmen. Die Lösung ist also alle Sätze zu löschen, bei denen RowNum und Rank unterschiedlich sind.

Diese Löschung geht über eine Common-Table-Expression und ist ansonsten sehr simpel:

WITH NumberedRows (Part, Ord, rownum, rnk) 
AS
(
SELECT Part, Ord,
   ROW_NUMBER()    OVER(ORDER BY Part, Ord ) AS rownum,
   RANK()                    OVER(ORDER BY Part, Ord ) AS rnk
FROM #Duplikate
)
DELETE FROM NumberedRows
WHERE rownum <> rnk;