To demonstrate the DENSE_RANK, we compare the ROW_NUMBER,
RANK, and DENSE_RANK functions in the query below to
highlight the differences in their output.
SELECT
employee_id,
first_name,
last_name,
salary,
ROW_NUMBER() OVER(ORDER BY salary DESC) as salary_order,
RANK() OVER(ORDER BY salary DESC) AS salary_rank,
DENSE_RANK() OVER(ORDER BY salary DESC) AS salary_dense_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;
Examining the output, we can see that the salary_dense_rank has a sequential ordering, without skipping any rank even
when one or more observations have the same salary.