SQL Window Functions Masterclass: ROW_NUMBER, RANK, DENSE_RANK, and Frame Specs

Window functions perform calculations across a set of table rows related to the current row without collapsing the result set into a single summary row. Practice SQL window functions live at /sql/sql-window-functions/.

ROW_NUMBER() vs RANK() vs DENSE_RANK()

SELECT employee_id, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num,
       RANK() OVER (ORDER BY salary DESC) as rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) as drk
FROM employees;

If salaries are 100, 100, 90: ROW_NUMBER produces 1, 2, 3. RANK produces 1, 1, 3 (skips 2). DENSE_RANK produces 1, 1, 2 (no gaps).