Oracle: RANK() vs. DENSE_RANK() Function

By Atul Rai | Last Updated: August 5, 2023 Previous       Next

In Oracle SQL, both RANK() and DENSE_RANK() are window functions that are used to assign a ranking to each row within a result set based on the specified criteria. However, there is a subtle difference in how they handle ties (i.e., when multiple rows have the same values for the ranking criteria).

1. RANK()

The RANK() function assigns a unique rank to each distinct row in the result set. If two or more rows have the same values for the ranking criteria, they receive the same rank, and the next rank is skipped. After that, the subsequent rank will be adjusted accordingly. In other words, there can be gaps in the ranking sequence when ties are encountered.

2. DENSE_RANK()

The DENSE_RANK() function also assigns a unique rank to each distinct row in the result set, like the RANK() function. However, when there are ties, DENSE_RANK() does not skip ranks; instead, it assigns the same rank to all tied rows, and the ranking sequence continues uninterrupted. Therefore, there are no gaps in the ranking sequence when using DENSE_RANK().

Let’s illustrate the difference between RANK() and DENSE_RANK() with an example:

Consider the following “Employees” table:

Using RANK() function:

SELECT
    EmployeeName,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM
    Employees;

Result:

As you can see, when using RANK(), two employees (John and Bob) with a salary of 4000 received the same rank (3), and the next rank (4) was skipped. Then, employees Jane and Mike with a salary of 3500 received the same rank (5).

Using DENSE_RANK() function:

SELECT
    EmployeeName,
    Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS Dense_Rank
FROM
    Employees;

Result:

With DENSE_RANK(), when two employees (John and Bob) with a salary of 4000 received the same rank (3), the next rank was not skipped. Instead, the subsequent employee (Jane) with a salary of 3500 received the next rank (4), resulting in no gaps in the ranking sequence.

Summary

So, the main difference is that DENSE_RANK() handles ties without skipping ranks, while RANK() may skip ranks when ties occur.

References

1. Oracle: RANK() Function
2. Oracle: DENSE_RANK() Function
3. RANK- OracleDoc
4. DENSE_RANK()- OracleDoc