ROW_NUMBER (Transact-SQL)

Article
10/31/2024

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) SQL analytics endpoint in Microsoft Fabric Warehouse in Microsoft Fabric

Numérote la sortie d’un jeu de résultats. Plus particulièrement, retourne le numéro séquentiel d’une ligne dans une partition d’un jeu de résultats, en commençant à 1 pour la première ligne de chaque partition.

ROW_NUMBER et RANK sont similaires. ROW_NUMBER numérote toutes les lignes dans l’ordre (par exemple 1, 2, 3, 4, 5). RANK fournit la même valeur numérique pour les liens (par exemple 1, 2, 2, 4, 5).

Notes

ROW_NUMBER est une valeur temporaire calculée lorsque la requête est exécutée. Pour conserver les nombres dans un tableau, consultez IDENTITY (propriété) et SEQUENCE.

Conventions de la syntaxe Transact-SQL

Syntaxe

ROW_NUMBER ( )   
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause )

Arguments

PARTITION BY value_expression
Divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction ROW_NUMBER est appliquée. value_expression spécifie la colonne par laquelle le jeu de résultats est partitionné. Si PARTITION BY n’est pas spécifié, la fonction traite toutes les lignes du jeu de résultats de la requête comme un seul groupe. Pour plus d’informations, consultez Clause OVER (Transact-SQL).

order_by_clause
La clause ORDER BY détermine la séquence dans laquelle les lignes d’une partition spécifique reçoivent leur valeur ROW_NUMBER unique. Elle est obligatoire. Pour plus d’informations, consultez Clause OVER (Transact-SQL).

Types de retour

bigint

Remarques d'ordre général

Rien ne garantit que les lignes renvoyées par une requête utilisant ROW_NUMBER() seront ordonnées exactement de la même manière à chaque exécution, sauf si les conditions suivantes sont vérifiées.

Les valeurs de la colonne partitionnée sont uniques.
Les valeurs des colonnes ORDER BY sont uniques.
Les combinaisons de valeurs de la colonne de partition et des colonnes ORDER BY sont uniques.

Si les ORDER BY colonnes ne sont pas uniques dans les résultats, envisagez d’utiliser RANK() ou DENSE_RANK().

ROW_NUMBER() n’est pas déterministe. Pour plus d’informations, consultez Fonctions déterministes et non déterministes.

Exemples

R. Exemples simples

La requête suivante retourne les quatre tables système dans l’ordre alphabétique.

SELECT 
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5
ORDER BY name ASC;

Voici le jeu de résultats.

name	recovery_model_desc
master	SIMPLE
model	FULL
msdb	SIMPLE
tempdb	SIMPLE

Pour ajouter une colonne de numéro de ligne devant chaque ligne, ajoutez une colonne avec la fonction ROW_NUMBER, appelée dans ce cas Row#. Vous devez déplacer la clause ORDER BY vers le haut jusqu’à la clause OVER.

SELECT 
  ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#,
  name, recovery_model_desc
FROM sys.databases 
WHERE database_id < 5;

Voici le jeu de résultats.

Row#	name	recovery_model_desc
1	master	SIMPLE
2	model	FULL
3	msdb	SIMPLE
4	tempdb	SIMPLE

La PARTITION BY clause de la recovery_model_desc colonne redémarre la numérotation lorsque la recovery_model_desc valeur change.

SELECT 
  ROW_NUMBER() OVER(PARTITION BY recovery_model_desc ORDER BY name ASC) 
    AS Row#,
  name, recovery_model_desc
FROM sys.databases WHERE database_id < 5;

Voici le jeu de résultats.

Row#	name	recovery_model_desc
1	model	FULL
1	master	SIMPLE
2	msdb	SIMPLE
3	tempdb	SIMPLE

B. Retour du nombre de lignes pour les vendeurs

L'exemple suivant calcule un numéro de ligne pour les vendeurs de Adventure Works Cycles en fonction de leur classement de ventes de l'année.

USE AdventureWorks2022;   
GO  
SELECT ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS Row,   
    FirstName, LastName, ROUND(SalesYTD,2,1) AS "Sales YTD"   
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Voici le jeu de résultats.

  
Row FirstName    LastName               SalesYTD  
--- -----------  ---------------------- -----------------  
1   Linda        Mitchell               4251368.54  
2   Jae          Pak                    4116871.22  
3   Michael      Blythe                 3763178.17  
4   Jillian      Carson                 3189418.36  
5   Ranjit       Varkey Chudukatil      3121616.32  
6   José         Saraiva                2604540.71  
7   Shu          Ito                    2458535.61  
8   Tsvi         Reiter                 2315185.61  
9   Rachel       Valdez                 1827066.71  
10  Tete         Mensa-Annan            1576562.19  
11  David        Campbell               1573012.93  
12  Garrett      Vargas                 1453719.46  
13  Lynn         Tsoflias               1421810.92  
14  Pamela       Ansman-Wolfe           1352577.13

C. Retour d'un sous-ensemble de lignes

L'exemple suivant calcule les numéros de ligne pour toutes les lignes de la table de SalesOrderHeader dans l'ordre d'OrderDate et retourne uniquement les lignes 50 à 60 inclus.

USE AdventureWorks2022;  
GO  
WITH OrderedOrders AS  
(  
    SELECT SalesOrderID, OrderDate,  
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber  
    FROM Sales.SalesOrderHeader   
)   
SELECT SalesOrderID, OrderDate, RowNumber    
FROM OrderedOrders   
WHERE RowNumber BETWEEN 50 AND 60;

D. Utilisation de Using ROW_NUMBER() avec PARTITION

L'exemple suivant utilise l'argument PARTITION BY pour partitionner le jeu de résultats d'une requête par la colonne TerritoryName. La clause ORDER BY spécifiée dans la clause OVER classe les lignes de chaque partition par la colonne SalesYTD. La clause ORDER BY dans l'instruction SELECT détermine l'ordre du jeu de résultats de la requête entier par TerritoryName.

USE AdventureWorks2022;  
GO  
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,  
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) 
  AS Row  
FROM Sales.vSalesPerson  
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0  
ORDER BY TerritoryName;

Voici le jeu de résultats.

  
FirstName  LastName             TerritoryName        SalesYTD      Row  
---------  -------------------- ------------------   ------------  ---  
Lynn       Tsoflias             Australia            1421810.92    1  
José       Saraiva              Canada               2604540.71    1  
Garrett    Vargas               Canada               1453719.46    2  
Jillian    Carson               Central              3189418.36    1  
Ranjit     Varkey Chudukatil    France               3121616.32    1  
Rachel     Valdez               Germany              1827066.71    1  
Michael    Blythe               Northeast            3763178.17    1  
Tete       Mensa-Annan          Northwest            1576562.19    1  
David      Campbell             Northwest            1573012.93    2  
Pamela     Ansman-Wolfe         Northwest            1352577.13    3  
Tsvi       Reiter               Southeast            2315185.61    1  
Linda      Mitchell             Southwest            4251368.54    1  
Shu        Ito                  Southwest            2458535.61    2  
Jae        Pak                  United Kingdom       4116871.22    1

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

E. Retour du nombre de lignes pour les vendeurs

L’exemple suivant retourne la valeur ROW_NUMBER des représentants commerciaux en fonction de leur quota de ventes assigné.

-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(ORDER BY SUM(SalesAmountQuota) DESC) 
    AS RowNumber,  
    FirstName, LastName,   
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName;

Voici un jeu de résultats partiel.


RowNumber  FirstName  LastName            SalesQuota  
---------  ---------  ------------------  -------------  
1          Jillian    Carson              12,198,000.00  
2          Linda      Mitchell            11,786,000.00  
3          Michael    Blythe              11,162,000.00  
4          Jae        Pak                 10,514,000.00

F. Utilisation de Using ROW_NUMBER() avec PARTITION

L'exemple suivant illustre l'utilisation de la fonction ROW_NUMBER avec l'argument PARTITION BY. Dans ce cas, la fonction ROW_NUMBER numérote les lignes dans chaque partition.

-- Uses AdventureWorks  
  
SELECT ROW_NUMBER() OVER(PARTITION BY SalesTerritoryKey 
        ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,  
    LastName, SalesTerritoryKey AS Territory,  
    CONVERT(varchar(13), SUM(SalesAmountQuota),1) AS SalesQuota   
FROM dbo.DimEmployee AS e  
INNER JOIN dbo.FactSalesQuota AS sq  
    ON e.EmployeeKey = sq.EmployeeKey  
WHERE e.SalesPersonFlag = 1  
GROUP BY LastName, FirstName, SalesTerritoryKey;

Voici un jeu de résultats partiel.

 
RowNumber  LastName            Territory  SalesQuota  
---------  ------------------  ---------  -------------  
1          Campbell            1           4,025,000.00  
2          Ansman-Wolfe        1           3,551,000.00  
3          Mensa-Annan         1           2,275,000.00  
1          Blythe              2          11,162,000.00  
1          Carson              3          12,198,000.00  
1          Mitchell            4          11,786,000.00  
2          Ito                 4           7,804,000.00

Voir aussi

RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL)

Partager via

ROW_NUMBER (Transact-SQL)

Syntaxe

Arguments

Types de retour

Remarques d'ordre général

Exemples

R. Exemples simples

B. Retour du nombre de lignes pour les vendeurs

C. Retour d'un sous-ensemble de lignes

D. Utilisation de Using ROW_NUMBER() avec PARTITION

Exemples : Azure Synapse Analytics et Analytics Platform System (PDW)

E. Retour du nombre de lignes pour les vendeurs

F. Utilisation de Using ROW_NUMBER() avec PARTITION

Voir aussi

Commentaires

Ressources supplémentaires