Calculates the value of a rank in a group of values. For 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 for the WINDOW clause. See 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 The RANK function is part of optional SQL/2008 language feature T612, "Advanced OLAP operations".
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.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |