ROW_NUMBER (Transact-SQL)

Applies to: 👁 Image
SQL Server 👁 Image
Azure SQL Database 👁 Image
Azure SQL Managed Instance 👁 Image
Azure Synapse Analytics 👁 Image
Analytics Platform System (PDW) 👁 Image
SQL analytics endpoint in Microsoft Fabric 👁 Image
Warehouse in Microsoft Fabric 👁 Image
SQL database in Microsoft Fabric

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

ROW_NUMBER and RANK are similar. ROW_NUMBER numbers all rows sequentially (for example 1, 2, 3, 4, 5). RANK provides the same numeric value for ties (for example 1, 2, 2, 4, 5).

👁 Image
Transact-SQL syntax conventions

Syntax

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

Arguments

PARTITION BY value_expression

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. value_expression specifies the column by which the result set is partitioned. If PARTITION BY is not specified, the function treats all rows of the query result set as a single group. For more information, see OVER Clause (Transact-SQL).

order_by_clause

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition. It is required. For more information, see OVER Clause (Transact-SQL).

Return types

bigint

Remarks

There is no guarantee that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution unless the following conditions are true.

• Values of the partitioned column are unique.

• Values of the ORDER BY columns are unique.

• Combinations of values of the partition column and ORDER BY columns are unique.

If the ORDER BY columns are not unique within the results, consider using RANK() or DENSE_RANK().

ROW_NUMBER() is nondeterministic. For more information, see Deterministic and Nondeterministic Functions.

For window function performance tuning guidance, see OVER() Performance considerations.

Examples

A. Simple examples

The following query returns the four system tables in alphabetic order.

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

Here's the result set.

To add a row number column in front of each row, add a column with the ROW_NUMBER function, in this case named Row#. You must move the ORDER BY clause up to the OVER clause.

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

Here's the result set.

The PARTITION BY clause on the recovery_model_desc column, restarts the numbering when the recovery_model_desc value changes.

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;

Here's the result set.

B. Return the row number for salespeople

The following example calculates a row number for the salespeople in Adventure Works Cycles based on their year-to-date sales ranking.

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; 

Here's the result set.

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. Return a subset of rows

The following example calculates row numbers for all rows in the SalesOrderHeader table in the order of the OrderDate and returns only rows 50 to 60 inclusive.

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. Use ROW_NUMBER() with PARTITION

The following example uses the PARTITION BY argument to partition the query result set by the column TerritoryName. The ORDER BY clause specified in the OVER clause orders the rows in each partition by the column SalesYTD. The ORDER BY clause in the SELECT statement orders the entire query result set by 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; 

Here's the result set.

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 

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

E. Return the row number for salespeople

The following example returns the ROW_NUMBER for sales representatives based on their assigned sales quota.

-- 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; 

Here is a partial result set.

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. Use ROW_NUMBER() with PARTITION

The following example shows using the ROW_NUMBER function with the PARTITION BY argument. This causes the ROW_NUMBER function to number the rows in each 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; 

Here is a partial result set.

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 

Related content

• SELECT - OVER clause (Transact-SQL)
• RANK (Transact-SQL)
• DENSE_RANK (Transact-SQL)
• NTILE (Transact-SQL)