Difference between rank and dense_rank Oracle

What is the difference between RANK and DENSE_RANK functions in Oracle ?

In simple words both does the same except RANK skips if there is a tie and DENSE_RANK does not. So remember to use DENSE_RANK to use in Nth largest queries.

Detailed explanation of RANK and DESNSE_RANK given in another posts

RANK Function


Example :-

Consider below emp table data

10 Bill 12000 5
11 Solomon 10000 5
12 Susan 10000 5
13 Wendy 9000 1
14 Benjamin 7500 1
15 Tom 7600 1
16 Henry 8500 2
17 Robert 9500 2
18 Paul 7700 2

select empno,salary,
rank() over(order by salary asc) “rank”,
dense_rank() over(order by salary asc) “dense_rank”
from emp;

See the result

Difference between RANK and DENSE_RANK
Difference between RANK and DENSE_RANK

RANK function skips number 8 while DENSE_RANK does not.

2 responses to “Difference between rank and dense_rank Oracle”

  1. thank you..very good example…