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.
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: