Oracle DENSE_RANK Function

DENSE_RANK ranks rows like RANK, giving tied rows the same rank, but without gaps afterward: 1, 2, 2, 3. That makes the rank count distinct values, which is exactly what questions such as "the second-highest salary" need.

Code for This Guide

The main examples are in the examples/analytic-functions folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dense_rank() over ([partition by expr [, ...]] order by expr [, ...])

The dense rank of a row is 1 plus the number of distinct ORDER BY values before it.

No Gaps after Ties

Example:

select customer_id, count(*) as bookings,
       row_number() over (order by count(*) desc) as row_number,
       rank()       over (order by count(*) desc) as rank,
       dense_rank() over (order by count(*) desc) as dense_rank
from   bookings
group  by customer_id
order  by bookings desc, customer_id
fetch  first 9 rows only;

Output:

   CUSTOMER_ID    BOOKINGS    ROW_NUMBER    RANK    DENSE_RANK
______________ ___________ _____________ _______ _____________
             5          15             1       1             1
             1          12             2       2             2
             3          12             3       2             2
             4          12             7       2             2
            18          12             5       2             2
            21          12             6       2             2
            49          12             4       2             2
            11          11            11       8             3
            13          11            10       8             3

9 rows selected.

Six customers share dense rank 2, and the customers with 11 bookings get 3, where RANK gives them 8.

The Second-Highest Salary

Because DENSE_RANK counts distinct values, DENSE_RANK = 2 finds the second-highest salary even when several people share the highest.

Example:

-- the second-highest distinct salary of each department
select department_id, salary as second_highest
from   (select distinct department_id, salary,
               dense_rank() over (partition by department_id order by salary desc) as dr
        from   employees
        where  department_id in (20, 30, 40))
where  dr = 2
order  by department_id;

Output:

   DEPARTMENT_ID    SECOND_HIGHEST
________________ _________________
              20             25500
              30             11800
              40             21000

DISTINCT in the inline view returns each salary once; without it, every employee with that salary would be listed.

Things to Know

• Use DENSE_RANK for "Nth distinct value" questions, RANK for competition-style places, and ROW_NUMBER for one row per position.
• In KEEP (DENSE_RANK FIRST ...), the same ranking selects the first or last rows of a group in an aggregate.
• Like the other ranking functions, it requires ORDER BY in the OVER clause.

Related Guides

• Oracle RANK Function
• How to Use KEEP FIRST in Oracle Aggregates

Conclusion

DENSE_RANK gives tied rows the same rank and continues without gaps, so ranks count distinct values. Use it to find the Nth-highest value per group, and choose RANK or ROW_NUMBER when gaps or unique numbers are wanted.