ROW_NUMBER (Transact-SQL)

ROW_NUMBER (Transact-SQL)

Community Content

In this section

Statistics Annotations (0)

Click here to expand

Collapse All

Language Filter

Other versions are also available for the following:

SQL Server 2005

ROW_NUMBER (Transact-SQL)

Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Topic link icon 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

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.

Examples

A. 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 AdventureWorks2012; 
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

B. 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 AdventureWorks2012;
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;

C. 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 AdventureWorks2012;
GO
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1),
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
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

Reference

RANK (Transact-SQL)

DENSE_RANK (Transact-SQL)

NTILE (Transact-SQL)