ROW_NUMBER (Transact-SQL)

SQL Server 2005 Books Online (November 2008)

Updated: 17 July 2006

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_clause> ] <order_by_clause> )

Arguments

<partition_by_clause>: Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the syntax of PARTITION BY, see OVER Clause (Transact-SQL).

<order_by_clause>: Determines the order in which the ROW_NUMBER value is assigned to the rows in a partition. For more information, see ORDER BY Clause (Transact-SQL). An integer cannot represent a column when the <order_by_clause> is used in a ranking function.

Return Types

bigint

Remarks

The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.

Examples

Note:
The `ORDER BY` in the `OVER` clause orders `ROW_NUMBER`. If you add an `ORDER BY` clause to the `SELECT` statement that orders by a column(s) other than `'Row Number' the` result set will be ordered by the outer `ORDER BY`.

The following example returns the ROW_NUMBER for the salespeople in AdventureWorks based on the year-to-date sales.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

The following example returns rows with numbers 50 to 60 inclusive in the order of the OrderDate.

USE AdventureWorks;
GO
WITH OrderedOrders AS
(
    SELECT SalesOrderID, OrderDate,
    ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
    FROM Sales.SalesOrderHeader 
) 
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 50 AND 60;

The following example shows using the PARTITION BY argument.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER 
    (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;

Change History

Release	History
17 July 2006	New content: Added example for PARTITION BY argument.

Reference

RANK (Transact-SQL)
DENSE_RANK (Transact-SQL)
NTILE (Transact-SQL)
Ranking Functions (Transact-SQL)
Functions (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance