A window function performs a calculation across a set of rows related to the current row — but unlike GROUP BY, it keeps every row. That makes it the right tool for ranking, running totals, and comparing a row to its neighbours, which are awkward or impossible with plain aggregates.
OVER, PARTITION BY, ORDER BY
You write an aggregate or ranking function followed by OVER (PARTITION BY ... ORDER BY ...). PARTITION BY splits rows into groups (like GROUP BY but without collapsing); ORDER BY orders rows within each partition for ranking and running calculations.
Rank employees by salary within each department
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drnk,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
FROM employees;Running total and previous-row comparison
SELECT name, salary,
SUM(salary) OVER (ORDER BY emp_id) AS running_total,
LAG(salary) OVER (ORDER BY emp_id) AS prev_salary
FROM employees;⚡ The edge
- Window functions keep every row, unlike GROUP BY which collapses them — that's why they're perfect for ranking and running totals while still showing the detail.
- RANK leaves gaps after ties (1,2,2,4); DENSE_RANK does not (1,2,2,3); ROW_NUMBER is always unique (1,2,3,4). Pick by whether ties should share a rank and whether gaps matter.
Worked example
Find the highest-paid employee in each department.
- Rank employees by salary within each department using a window function partitioned by dept_id.
- ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) gives 1 to the top earner per department.
- Wrap it in a subquery/CTE and keep the rows where that number = 1 (window functions can't go in WHERE directly).
Answer: SELECT * FROM (SELECT name,dept_id,salary, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) rn FROM employees) t WHERE rn=1;
Worked example
What's the difference between RANK and DENSE_RANK on salaries 90,80,80,60?
- Both give the two 80s the same rank (2).
- RANK then skips to 4 for the next value (gap): 1,2,2,4.
- DENSE_RANK does not skip: 1,2,2,3.
Answer: RANK = 1,2,2,4 (gaps after ties); DENSE_RANK = 1,2,2,3 (no gaps).
⚠ Watch out
- You can't use a window function in WHERE or GROUP BY — compute it in a subquery/CTE, then filter the result.
- RANK leaves gaps after ties; DENSE_RANK doesn't; ROW_NUMBER never ties — choosing wrong gives wrong 'top-N'.
- Forgetting ORDER BY inside OVER makes ranking/running calculations meaningless.