What's the difference between RANK() and DENSE_RANK() functions in oracle?


What's the difference between RANK() and DENSE_RANK() functions? How to find out nth salary in the following emptbl table?



10       rrr    10000.00

11       nnn    20000.00

11       mmm    5000.00

12       kkk    30000.00

10       fff    40000.00

10       ddd    40000.00

10       bbb    50000.00

10       ccc    50000.00


If in the table data having nulls, what will happen if I want to find out the nth salary?




RANK(): The RANK() function helps in providing the ranking within the ordered partition. The ties are assigned with the same rank  with the next rankings being skipped. Hence, if you have three items at rank 2, then the next rank listed will be ranked 5.

DENSE_RANK(): The DENSE_RANK() function also gives the ranking within the ordered partition, but the ranks will be represented in the consecutive form. If there are ranks with the multiple items, then also no ranks will be skipped. For nulls, the ranking will be based on the ORDER BY clause.


If you want to unleash your potential in this competitive field, please visit the SQL course page for more information, where you can find the SQL tutorials and SQL frequently asked interview questions and answers as well.


This topic has been locked/unapproved. No replies allowed

Login to participate in this discussion.

Leave a reply

Before proceeding, please check your email for a verification link. If you did not receive the email, click here to request another.