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

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.

DENSE_RANK( ) OVER ( window-spec )

window-spec : see the Remarks section below

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.

• CUME_DIST function [Ranking]
• PERCENT_RANK function [Ranking]
• RANK function [Ranking]

• SQL/2003    SQL/OLAP feature T612

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: