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