ROW_NUMBER (Transact-SQL)

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).

Note

`ROW_NUMBER` is a temporary value calculated when the query is run. To persist numbers in a table, see IDENTITY Property and SEQUENCE.

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).

bigint

General 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.

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 is the result set.

name recovery_model_desc
master SIMPLE
model FULL
msdb SIMPLE
tempdb SIMPLE

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 is the result set.

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

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 is the result set.

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

B. Returning 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 is 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. Returning 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
)
SELECT SalesOrderID, OrderDate, RowNumber
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
``````

D. Using 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 is the result set.

``````
FirstName  LastName             TerritoryName        SalesYTD      Row
---------  -------------------- ------------------   ------------  ---
Lynn       Tsoflias             Australia            1421810.92    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. Returning 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. Using 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
``````