ROW_NUMBER (Transact-SQL)

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

bigint

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

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

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

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;

Reference