ROW_NUMBER (Transact-SQL)
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Point de terminaison analytique SQL dans Microsoft FabricEntrepôt dans 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 )
Remarque
Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.
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 BYsont uniques.Les combinaisons de valeurs de la colonne de partition et des colonnes
ORDER BYsont uniques.
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 obtenu.
| 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 obtenu.
| Row# | name | recovery_model_desc |
|---|---|---|
| 1 | master | SIMPLE |
| 2 | model | FULL |
| 3 | msdb | SIMPLE |
| 4 | tempdb | SIMPLE |
L’ajout d’une clause PARTITION BY sur la colonne recovery_model_desc redémarre la numérotation lorsque la valeur recovery_model_desc 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 obtenu.
| 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 obtenu.
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 obtenu.
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)
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d’informations, consultez https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour