ROW_NUMBER

Was ist ROW_NUMBER?

Die ROW_NUMBER Funktion ist eine Window Function, also eine Funktion, die die Daten sortiert in sogenannten „Fenstern“ oder „Partitionen“ abarbeitet. Die Sortierung und die Partitionen definiert ihr, indem ihr nach dem Funktionsaufruf die Schlüsselworte ORDER BY und PARTITION BY verwendet. Die ROW_NUMBER-Funktion nummeriert dann alle Zeilen in der Ergebnismenge, anfangend bei 1 aufsteigend durch. Dabei startet jede Partition wieder bei 1.

Wie könnt ihr mit ROW_NUMBER arbeiten?

Die ROW_NUMBER-Funktion könnt ihr in der Spaltenliste eurer Abfrage verwenden, wenn ihr die vergebenen Nummern sehen möchtet:

SELECT
     [CountryID]
    ,ROW_NUMBER() OVER (ORDER BY [CountryID]) as [RowNumber]
    ,[CountryName]
    ,[FormalName]
    ,[IsoAlpha3Code]
FROM [WideWorldImporters].[Application].[Countries]

Ihr seht nun eine Spalte mit der Überschrift RowNumber und einer fortlaufenden Zahl im Inhalt. Toll. Naja. Schauen wir mal, ob wir damit nicht etwas tun können. Wenn ihr euch das Ergebnis anschaut, seht ihr, dass schon früh in der Zeile von Albanien die CountryID von der RowNumber abweicht. Nun könnt ihr die ROW_NUMBER-Funktion natürlich verwenden, um die Datensätze (na gut, den Datensatz) zu finden, bei dem beide Werte übereinstimmen:

; WITH cte_rn as (
    SELECT
         [CountryID]
        ,ROW_NUMBER() OVER (ORDER BY [CountryID]) as [RowNumber]
        ,[CountryName]
        ,[FormalName]
        ,[IsoAlpha3Code]
    FROM [WideWorldImporters].[Application].[Countries]
)
SELECT 
     [CountryId]
    ,[CountryName]
    ,[FormalName]
    ,[IsoAlpha3Code]
FROM cte_rn 
WHERE [CountryID] = [RowNumber]

Ihr seht, dass am Ende nur noch eine Zeile übrigbleibt, nämlich die für Afghanistan. Bei allen anderen Zeilen weicht die Zeilenzahl von der ID ab. Wenn ihr fortlaufende IDs wollen würdet, könntet ihr nun mit der CTE die Tabelle aktualisieren und die CountryID entsprechend setzen (vorausgesetzt, dass hier keine IDENTITY-Spalte definiert wurde, sonst müsst ihr mit IDENTITY INSERT arbeiten).

Das ist alles ganz nett, doch was könnt ihr mit der ROW_NUMBER-Funktion nun wirklich tun? Vermutlich am häufigsten verwenden echte Ninjas die ROW_NUMBER-Funktion um in Kombination mit CTEs doppelte Zeilen aus einem Ergebnis herauszufiltern. Eine (zugegeben reichlich dämliche) Abfrage, anhand der ihr das sehen könnt, ist folgende aus der wir nun die Duplikate bei den Ländern filtern möchten:

SELECT 
     c.[CountryId]
    ,c.[CountryName]
    ,c.[FormalName]
    ,s.[StateProvinceID]
FROM [Application].[Countries] as c
LEFT JOIN [Application].[StateProvinces] as s 
ON c.[CountryID] = s.[CountryID]
ORDER BY c.CountryID

In der Ergebnismenge seht ihr hier, dass die USA mehrfach auftauchen (da mehrere Bundesstaaten in der StateProvinces-Tabelle definiert sind). Fügen wir nun also eine ROW_NUMBER hinzu, die die Bundesstaaten innerhalb eines Landes fortlaufend nummeriert:

SELECT 
     c.[CountryId]
    ,c.[CountryName]
    ,c.[FormalName]
    ,s.[StateProvinceID]
    ,ROW_NUMBER() OVER (PARTITION BY c.[CountryID] ORDER BY s.[StateProvinceID]) as [RowNumber]
FROM [Application].[Countries] as c
LEFT JOIN [Application].[StateProvinces] as s 
ON c.[CountryID] = s.[CountryID]

Ihr seht in der Ergebnismenge, dass bei allen Ländern, die keine Bundesstaaten kennen, nur ein NULL-Wert als Bundesstaat auftaucht (der LEFT JOIN also keinen Treffer hatte), bei den USA allerdings 53 Zeilen eingetragen wurden (welch ein Zufall). Beachtet dabei, dass wir ein PARTITION BY angegeben haben (anders als in den vorangegangenen Beispielen). Das bedeutet, wenn sich in der Ergebnismenge die CountryID ändert, beginnt unsere ROW_NUMBER-Funktion wieder von 1 zu zählen.

Wenn ihr hieraus nun eine Zeile filtern möchtet, dann könnt ihr dieses Verhalten nutzen, indem ihr nur die Einträge mit einer ROW_NUMBER 1 behaltet:

; with cte_rn as ( 
    SELECT 
         c.[CountryId]
        ,c.[CountryName]
        ,c.[FormalName]
        ,s.[StateProvinceID]
        ,ROW_NUMBER() OVER (PARTITION BY c.[CountryID] ORDER BY s.[StateProvinceID]) as [RowNumber]
    FROM [Application].[Countries] as c
    LEFT JOIN [Application].[StateProvinces] as s 
    ON c.[CountryID] = s.[CountryID]
)
select 
     [CountryID]
    ,[CountryName]
    ,[FormalName]
    ,[StateProvinceID]
    ,[RowNumber]
FROM cte_rn
WHERE [RowNumber] = 1

In eurer Ergebnismenge ist nun wieder nur eine Zeile für die USA enthalten.

Mit Hilfe dieses Patterns könnt ihr nun in allen möglichen und unmöglichen Lebenslagen Duplikate aus Tabellen filtern. Beachtet aber, dass es natürlich besser wäre, die Abfragen von vorneherein so zu schreiben, dass gar keine ungewollten Duplikate entstehen.

Referenzen

• Offizielle Dokumentation von Microsoft