ROW_NUMBER vs RANK vs DENSE_RANK in SQL Explained

Question

Given an `employee` table with duplicate salary values, write a query to compute `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()` ordered by `salary DESC`. Contrast the resulting rankings.

Accepted Answer

Optimal Solution:
SELECT 
  emp_name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank_val
FROM employee
ORDER BY salary DESC;

Technical Explanation:
ROW_NUMBER assigns strictly unique integers (1,2,3,4). RANK assigns identical rank to ties but skips subsequent numbers (1,2,2,4). DENSE_RANK assigns identical ranks to ties without skipping numbers (1,2,2,3).

emp_name	salary
Alice	10000
Bob	9000
Charlie	9000
Dave	8000

emp_name	salary	row_num	rank_val	dense_rank_val
Alice	10000	1	1	1
Bob	9000	2	2	2
Charlie	9000	3	2	2
Dave	8000	4	4	3