Assigns a unique number to each row. Use this function instead of the NUMBER function.
ROW_NUMBER( ) OVER ( window-spec )
window-spec : see the Remarks section below
INTEGER
Elements of window-spec can be specified either in the function syntax (inline), or in conjunction with a WINDOW clause in the SELECT statement. When used as a window function, you must specify an ORDER BY clause, you may specify a PARTITION BY clause, however, you can not specify a ROWS or RANGE clause. See the window-spec definition provided in WINDOW clause.
For more information about using window functions in SELECT statements, including working examples, see Window functions.
For more information about specifying a window specification in an OVER clause, see Window definition: inlining using the OVER clause and WINDOW clause.
SQL/2008 ROW_NUMBER is part of optional SQL/2008 language feature T611, "Elementary OLAP operations".
The following example returns a result set that provides unique row numbers for each employee in New York and Utah. Because the query is ordered by Salary in descending order, the first row number is given to the employee with the highest salary in the data set. Although two employees have identical salaries, the tie is not resolved because the two employees are assigned unique row numbers.
SELECT Surname, Salary, State, ROW_NUMBER() OVER (ORDER BY Salary DESC) "Rank" FROM Employees WHERE State IN ('NY','UT'); |
Surname | Salary | State | Rank |
---|---|---|---|
Shishov | 72995.000 | UT | 1 |
Wang | 68400.000 | UT | 2 |
Cobb | 62000.000 | UT | 3 |
Morris | 61300.000 | UT | 4 |
Davidson | 57090.000 | NY | 5 |
Martel | 55700.000 | NY | 6 |
Blaikie | 54900.000 | NY | 7 |
Diaz | 54900.000 | NY | 8 |
Driscoll | 48023.690 | UT | 9 |
Hildebrand | 45829.000 | UT | 10 |
... | ... | ... | ... |
Lynch | 24903.000 | UT | 19 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |