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.
A. Returning the row number for salespeople
The following example returns the ROW_NUMBER for the salespeople in AdventureWorks 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 AdventureWorks;
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;
Any fetch of data without such an ordered index would yield a different ordered result each time (aside of short term caching perhaps). Adding rownumbers to such datasets will not allow for any reliable back-referencing to the datarows. You'll simply need a PK for that, ordered unique index, or (default) order clause over distinct values.
----------
edit: Actually, a post by Ghanaya is correct - since RAND() returns the same number for each row, ordering by that will return the actual row count.
Select *, ROW_NUMBER() OVER (ORDER BY rand()) AS RowNum from emp5
This returns rows in actual order and is fast even if there is no PK column
