Home Library Learn Downloads Support Community
Sign in | United States - English | |
20 out of 31 rated this helpful - Rate this topic

ROW_NUMBER (Transact-SQL)

SQL Server 2008 R2

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

Copy 

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.

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.

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

Copy 
USE AdventureWorks2008R2;
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;

C. Using ROW_NUMBER() with PARTITION

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

Copy 
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;
Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
More useful example

The given example for row_number over partition achieves little as postal codes are unique for all salespeople. Note the last 'order by' partition overrides the previous

use AdventureWorks2008
go
SELECT TerritoryGroup, TerritoryName,
       FirstName, LastName, 
       SalesLastYear,
       ROW_NUMBER() OVER(PARTITION BY TerritoryGroup ORDER BY SalesLastYear DESC) AS 'Rank Last Year',
       SalesYTD,
       ROW_NUMBER() OVER(PARTITION BY TerritoryGroup ORDER BY SalesYTD DESC) AS 'Sales Rank'
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL 
  AND SalesYTD <> 0;
History