DENSE_RANK

Purpose

This function returns the rank of a row within an ordered partition.

Syntax

dense_rank::=

Usage Notes

  • DENSE_RANK is only available as an analytic function (i.e., in combination with over_clause). For more information, refer to Analytic Functions section.
  • The over_clause has to contain an order_clause part and must not contain a window_frame_clause.
  • The same value is returned for rows with equal ranking. However, there are no gaps in the function results after duplicate values. This is different from RANK.

Example

Analytic Functions

SELECT 
    id, department, current_salary, 
    DENSE_RANK() OVER (PARTITION BY department ORDER BY current_salary) DENSE_RANK 
FROM employee_table ORDER BY department, current_salary;
Result
ID DEPARTMENT CURRENT_SALARY DENSE_RANK
2001 ACCOUNTS 55000 1
2002 ACCOUNTS 65000 2
2003 ACCOUNTS 70000 3
2004 ACCOUNTS 80000 4
2005 ACCOUNTS 80000 4
1001 HR 55000 1
1002 HR 70000 2
1004 HR 70000 2
1003 HR 90000 3