Skip to Main Content
  • Questions

  • Analytical function RANK() vs. DENSE_RANK()

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Question and Answer

Chris Saxon

Thanks for the question, Saket.

Asked: March 05, 2018 - 6:10 am UTC

Last updated: March 05, 2018 - 10:53 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi, I have a very plain question about Analytical functions RANK() and DENSE_RANK().
Can you pls tell me a real scenario when we would use "RANK() and why not DENSE_RANK()" and vice versa. Something other than the students rank calculation. Thanks in advance.

and Chris said...

Dense_rank always assigns consecutive values.

Rank gives the "Olympic rank". So when many rows have the same value, the next after value will start at the Nth row:

update hr.employees
set    salary = 7000
where  department_id = 100
and    salary <= 7800;

select salary, 
       rank() over (order by salary) rk, 
       dense_rank() over (order by salary) dr
from   hr.employees
where  department_id = 100;

SALARY   RK   DR   
    7000    1    1 
    7000    1    1 
    7000    1    1 
    8200    4    2 
    9000    5    3 
   12008    6    4


Connor discusses this further in this video:



Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.