SQL Window Functions: The Complete Guide

SELECT 
  department,
  name,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

RANK() assigns a ranking number to each row within its department partition, ordered by salary descending. The highest salary gets rank 1.

1. RANK() — the window function that assigns rankings
2. OVER (...) — defines the window (which rows to consider)
3. PARTITION BY department — restart ranking for each department
4. ORDER BY salary DESC — rank from highest to lowest salary
5. Tied salaries get the same rank; next rank skips (1,1,3 not 1,1,2)

• RANK vs DENSE_RANK: RANK skips numbers after ties (1,1,3), DENSE_RANK doesn't (1,1,2)
• ROW_NUMBER always gives unique numbers even for ties (arbitrary tiebreaker)
• NULL salaries are typically ranked last (database-dependent)
• Window functions cannot be used in WHERE — use a subquery or CTE