|
The ROW_NUMBER function uniquely numbers the rows in its result. It is not a ranking function; however, you can use it in any situation in which you can use a ranking function, and it behaves similarly to a ranking function.
For example, you can use ROW_NUMBER in a derived table so that additional restrictions, even joins, can be made over the ROW_NUMBER values:
SELECT *
FROM ( SELECT Description, Quantity,
ROW_NUMBER() OVER ( ORDER BY ID ASC ) AS RowNum
FROM Products ) AS DT
WHERE RowNum <= 3
ORDER BY RowNum;
This query returns the following results:
| Description | Quantity | RowNum |
|---|---|---|
| Tank Top | 28 | 1 |
| V-neck | 54 | 2 |
| Crew Neck | 75 | 3 |
As with the ranking functions, ROW_NUMBER requires an ORDER BY clause.
As well, ROW_NUMBER can return non-deterministic results when the window's ORDER BY clause is over non-unique expressions; row order is unpredictable in the case of ties.
ROW_NUMBER is designed to work only over the entire partition; hence, a ROWS or RANGE clause cannot be specified with a ROW_NUMBER function.
For more information on the syntax for the ROW_NUMBER function, see ROW_NUMBER function [Miscellaneous].