RANK() / DENSE_RANK()

RANK() and DENSE_RANK() are window functions that number rows within an ordered partition, both giving tied rows the same rank — but RANK() then skips the next rank number(s), while DENSE_RANK() continues consecutively with no gaps.

With salaries 9000, 6000, 6000, 4800: RANK() gives 1, 2, 2, 4 (skipping 3, since two rows tied for 2nd). DENSE_RANK() gives 1, 2, 2, 3 (no gap). Use DENSE_RANK() when you want "the Nth distinct value" (like the classic Nth-highest-salary problem); use RANK() when ties should genuinely consume rank positions, competition-style.

Both differ from ROW_NUMBER(), which never ties — it assigns a unique sequential number to every row regardless of duplicate values.

SELECT first_name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;