Introduction
Window functions perform a calculation across a set of rows related to the current row -- called a 'window' -- without collapsing the result into a single row the way GROUP BY does. Each input row keeps its own identity in the output, but gains an additional computed column such as a rank, running total, or lag/lead value. Window functions are defined using the OVER() clause, which can specify how rows are partitioned into groups and how they are ordered within each partition.
Cricket analogy: A window function is like showing each batsman's individual score alongside their running series total, without collapsing the scorecard into one team row the way a total-runs summary would -- OVER() lets you partition by team and order by innings.
Syntax
SELECT
emp_id,
dept_id,
salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_num,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_num,
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total_salary
FROM employees;Explanation
PARTITION BY divides the rows into independent groups (like GROUP BY, but without collapsing rows), and ORDER BY within OVER() determines the sequence used for ranking or running calculations within each partition. The three ranking functions differ specifically in how they handle ties: ROW_NUMBER() always assigns a unique, strictly sequential integer to every row regardless of ties, so two equal salaries still get different numbers (e.g. 1, 2, 3). RANK() assigns the same rank to tied rows but then skips the next rank number(s) by the count of ties (e.g. 1, 1, 3). DENSE_RANK() also assigns the same rank to tied rows but does not skip any subsequent rank numbers (e.g. 1, 1, 2). Aggregate functions like SUM(), AVG(), COUNT() used with OVER() (and no ORDER BY, or with a frame clause) compute per-partition aggregates while still returning one row per input row.
Cricket analogy: PARTITION BY team groups batsmen without merging their rows, and ORDER BY runs sets ranking order: ROW_NUMBER() gives each batsman a unique slot even if two tie on 50 runs, RANK() gives both 50-scorers rank 1 then jumps to 3, DENSE_RANK() gives them rank 1 then 2, and SUM() OVER() shows each row its team's total runs.
Example
-- Rank employees by salary within each department, and show a running total
SELECT
dept_id,
name,
salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS salary_rank,
SUM(salary) OVER (
PARTITION BY dept_id
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
LAG(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS prev_salary
FROM employees;Output
For a department with salaries 90000, 90000, 80000: RANK() OVER (ORDER BY salary DESC) produces 1, 1, 3 for the two tied top earners followed by the next distinct salary, while DENSE_RANK() would instead produce 1, 1, 2. running_total accumulates the salary sum row by row within each department as ordered by the frame clause, and LAG(salary) returns the salary of the previous row in that department's ordering (NULL for the first row in each partition), which is useful for comparing a row to the one before it, such as computing period-over-period change.
Cricket analogy: For scores 90, 90, 80: RANK() gives the two tied batsmen rank 1 then jumps to rank 3 for the 80, while DENSE_RANK() gives 1, 1, 2; a running_total accumulates the team score ball by ball, and LAG(runs) shows the previous batsman's score, NULL for the first.
Key Takeaways
- Window functions compute values across a set of related rows via OVER(), without collapsing rows like GROUP BY does.
- PARTITION BY groups rows for independent calculation; ORDER BY within OVER() controls ranking and running-total sequence.
- ROW_NUMBER() gives unique sequential numbers even for ties; RANK() leaves gaps after ties; DENSE_RANK() leaves no gaps after ties.
- Frame clauses (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) control which rows are included in running aggregates.
- LAG() and LEAD() access prior/subsequent row values within a partition, commonly used for period-over-period comparisons.
Practice what you learned
1. For salaries 100, 100, 90 ordered descending, what values does RANK() produce?
2. For salaries 100, 100, 90 ordered descending, what values does DENSE_RANK() produce?
3. Which clause divides rows into independent groups for a window function without collapsing them into single output rows?
4. What does ROW_NUMBER() do differently from RANK() when there are tied values in the ORDER BY column?
Was this page helpful?
You May Also Like
Common Table Expressions (CTEs)
A named, temporary result set defined with WITH that improves readability and enables recursive queries.
GROUP BY and HAVING
Understand how GROUP BY partitions rows into groups and how HAVING filters those groups after aggregation, unlike WHERE.
Aggregate Functions in SQL
Learn how COUNT, SUM, AVG, MIN, and MAX summarize sets of rows into single values, and how each handles NULLs.