--- title: "ROW_NUMBER (Transact-SQL)" description: "Transact-SQL reference for the ROW_NUMBER function. This function numbers the output of a result set." author: MikeRayMSFT ms.author: mikeray ms.date: "09/11/2017" ms.service: sql ms.subservice: t-sql ms.topic: reference f1_keywords: - "ROW_NUMBER" - "ROW_NUMBER_TSQL" - "ROW_NUMBER()_TSQL" helpviewer_keywords: - "ROW_NUMBER function" - "row numbers [SQL Server]" - "sequential row numbers [SQL Server]" dev_langs: - "TSQL" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current||=fabric" --- # ROW_NUMBER (Transact-SQL) [!INCLUDE [sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw](../../includes/applies-to-version/sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw.md)] 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](../../t-sql/statements/create-table-transact-sql-identity-property.md) and [SEQUENCE](../../t-sql/statements/create-sequence-transact-sql.md). :::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: [Transact-SQL syntax conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ```syntaxsql ROW_NUMBER ( ) OVER ( [ PARTITION BY value_expression , ... [ n ] ] order_by_clause ) ``` ## Arguments PARTITION BY *value_expression* Divides the result set produced by the [FROM](../../t-sql/queries/from-transact-sql.md) 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)](../../t-sql/queries/select-over-clause-transact-sql.md). *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)](../../t-sql/queries/select-over-clause-transact-sql.md). ## Return Types **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](../../relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions.md). ## Examples ### A. Simple examples The following query returns the four system tables in alphabetic order. ```sql SELECT name, recovery_model_desc FROM sys.databases WHERE database_id < 5 ORDER BY name ASC; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] |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. ```sql SELECT ROW_NUMBER() OVER(ORDER BY name ASC) AS Row#, name, recovery_model_desc FROM sys.databases WHERE database_id < 5; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] |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. ```sql 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; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] |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 [!INCLUDE[ssSampleDBCoFull](../../includes/sssampledbcofull-md.md)] based on their year-to-date sales ranking. ```sql 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; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ``` 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. ```sql 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. 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`. ```sql 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; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ``` 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: [!INCLUDE[ssazuresynapse-md](../../includes/ssazuresynapse-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)] ### E. Returning the row number for salespeople The following example returns the `ROW_NUMBER` for sales representatives based on their assigned sales quota. ```sql -- 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. ```sql -- 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 ``` ## See Also [RANK (Transact-SQL)](../../t-sql/functions/rank-transact-sql.md) [DENSE_RANK (Transact-SQL)](../../t-sql/functions/dense-rank-transact-sql.md) [NTILE (Transact-SQL)](../../t-sql/functions/ntile-transact-sql.md)