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
Returns

INTEGER

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:

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
100 Blaikie 54900.000 NY 7
400 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