Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <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.
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.
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;