Calculates the rank of a value in a partition. For tied values, the DENSE_RANK function does not leave gaps in the ranking sequence.
DENSE_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. More information is available in the window-spec definition of the WINDOW clause.
SQL/2008 The DENSE_RANK function comprises part of optional SQL/2008 language feature T612, "Advanced OLAP operations".
SQL Anywhere supports SQL/2008 language feature F441, "Extended set function support", which permits operands of window functions to be arbitrary expressions that are not column references.
SQL Anywhere does not support optional SQL/2008 feature F442, "Mixed column references in set functions". SQL Anywhere does not permit the arguments of an aggregate function to include both a column reference from the query block containing the DENSE_RANK function, combined with an outer reference. For an example, see AVG function [Aggregate].
The following example returns a result set that provides a ranking of the employees' salaries in Utah and New York. Although 19 records are returned in the result set, only 18 rankings are listed because of a 7th-place tie between the 7th and 8th employee in the list, who have identical salaries. Instead of ranking the 9th employee as '9', the employee is listed as '8' because the DENSE_RANK function does not leave gaps in the ranks.
SELECT DepartmentID, Surname, Salary, State, DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank FROM Employees WHERE State IN ('NY','UT'); |
Here is the result set:
DepartmentID | Surname | Salary | State | SalaryRank |
---|---|---|---|---|
100 | Shishov | 72995.000 | UT | 1 |
100 | Wang | 68400.000 | UT | 2 |
100 | Cobb | 62000.000 | UT | 3 |
400 | Morris | 61300.000 | UT | 4 |
300 | Davidson | 57090.000 | NY | 5 |
200 | Martel | 55700.000 | NY | 6 |
400 | Blaikie | 54900.000 | NY | 7 |
100 | Diaz | 54900.000 | UT | 7 |
100 | Driscoll | 48023.000 | UT | 8 |
400 | Hildebrand | 45829.000 | UT | 9 |
100 | Whitney | 45700.000 | NY | 10 |
100 | Guevara | 42998.000 | NY | 11 |
100 | Soo | 39075.000 | NY | 12 |
200 | Goggin | 37900.000 | UT | 13 |
400 | Wetherby | 35745.000 | NY | 14 |
400 | Ahmed | 34992.000 | NY | 15 |
500 | Rebeiro | 34576.000 | UT | 16 |
300 | Bigelow | 31200.000 | UT | 17 |
500 | Lynch | 24903.000 | UT | 18 |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |