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.
Transact-SQL Syntax Conventions

Syntax:

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

Examples

---

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

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;