Export (0) Print
Expand All

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


ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )

<partition_by_clause>

Divides the result set produced by the FROM clause into partitions to which the ROW_NUMBER function is applied. For the PARTITION BY syntax, 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.

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

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.

  1. Values of the partitioned column are unique.

  2. Values of the ORDER BY columns are unique.

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

A. Returning the row number for salespeople

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

SELECT FirstName, LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS "Row Number", SalesYTD, PostalCode 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

B. Returning a subset of rows

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

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

C. Using ROW_NUMBER() with PARTITION

The following example shows using the ROW_NUMBER function with the PARTITION BY argument.

SELECT FirstName, LastName, ROW_NUMBER() OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number", SalesYTD, PostalCode 
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0;

Community Additions

ADD
Show:
© 2014 Microsoft