Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 11.0.0 » SQL Anywhere Server - SQL Reference » Using SQL » SQL functions » Alphabetical list of functions

 

DENSE_RANK function [Ranking]

Calculates the rank of a value in a partition. In the case of tied values, the DENSE_RANK function does not leave gaps in the ranking sequence.

Syntax
DENSE_RANK( ) OVER ( window-spec )
window-spec : see the Remarks section below
Remarks

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.

See also
Standards and compatibility
  • SQL/2003   SQL/OLAP feature T612.

Example

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:

Surname Salary State SalaryRank
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 UT 7
Driscoll 48023.000 UT 8
Hildebrand 45829.000 UT 9
Whitney 45700.000 NY 10
Guevara 42998.000 NY 11
Soo 39075.000 NY 12
Goggin 37900.000 UT 13
Wetherby 35745.000 NY 14
Ahmed 34992.000 NY 15
Rebeiro 34576.000 UT 16
Bigelow 31200.000 UT 17
Lynch 24903.000 UT 18