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 |