Calculates the value of a rank in a group of values. In the case of ties, the RANK function leaves a gap in the ranking sequence.
RANK( ) 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.
SQL/2003 SQL/OLAP feature T612.
The following example provides a rank in descending order of employees' salaries in Utah and New York. Notice that the 7th and 8th employees have an identical salary and therefore share the 7th place ranking. The employee that follows receives the 9th place ranking, which leaves a gap in the ranking sequence (no 8th place ranking).
SELECT Surname, Salary, State, RANK() 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 | 7 |
Driscoll | 48023.690 | UT | 9 |
Hildebrand | 45829.000 | UT | 10 |
Whitney | 45700.000 | NY | 11 |
... | ... | ... | ... |
Lynch | 24903.000 | UT | 19 |
Discuss this page in DocCommentXchange. Send feedback about this page using email. |
Copyright © 2009, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.1 |