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 for ties.
ROW_NUMBER is designed to work over the entire partition, so a ROWS or RANGE clause cannot be specified with a ROW_NUMBER function.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|