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 over the entire partition, so a ROWS or RANGE clause cannot be specified with a ROW_NUMBER function.
For more information about the syntax for the ROW_NUMBER function, see ROW_NUMBER function [Miscellaneous].
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |