The OVER Clause and Ranking Functions
Window functions compute a value across a set of related rows — the 'window' — without collapsing those rows into one, unlike GROUP BY. The OVER clause defines the window: OVER (PARTITION BY Region ORDER BY TotalAmount DESC) groups rows by Region and orders them within each group, and RANK(), DENSE_RANK(), and ROW_NUMBER() each number rows differently — RANK() leaves gaps after ties (1,1,3), DENSE_RANK() does not (1,1,2), and ROW_NUMBER() ignores ties entirely and assigns a strictly increasing integer to every row.
Cricket analogy: RANK() applied to a tournament's run-scorer list gives two batsmen tied on 450 runs both rank 2, then skips to rank 4 for the next player, while DENSE_RANK() would give that next player rank 3, and ROW_NUMBER() would arbitrarily break the tie and assign 2 and 3 regardless of the equal score.
SELECT
SalesRep,
Region,
TotalAmount,
RANK() OVER (PARTITION BY Region ORDER BY TotalAmount DESC) AS RegionRank,
DENSE_RANK() OVER (PARTITION BY Region ORDER BY TotalAmount DESC) AS RegionDenseRank,
ROW_NUMBER() OVER (PARTITION BY Region ORDER BY TotalAmount DESC) AS RegionRowNum
FROM Sales.Deals;Aggregate Window Functions and Frames
Aggregate functions like SUM, AVG, MIN, and MAX can also be used as window functions by adding OVER(...), which computes a running or partitioned total without collapsing rows — SUM(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) produces a running total per customer that updates row by row. The frame clause (ROWS BETWEEN ... AND ...) controls exactly which rows within the partition are included in the calculation; the default frame for an ordered window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which can behave unexpectedly with tied ORDER BY values compared to the more predictable ROWS-based frame.
Cricket analogy: SUM(Runs) OVER (PARTITION BY PlayerID ORDER BY MatchDate) produces a running career-runs total that updates after every match, the way a broadcast graphic shows a batsman's tally climbing innings by innings without collapsing the match log into one row.
Use an explicit ROWS BETWEEN frame (e.g. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW for a 3-row moving average) whenever you need precise physical-row control, since the default RANGE frame can silently include extra rows that share the same ORDER BY value as the current row.
LAG, LEAD, and Value Functions
LAG() and LEAD() access a value from a preceding or following row within the same ordered partition without a self join: LAG(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) returns the previous order's amount for the current row, enabling direct row-over-row comparisons like month-over-month change. FIRST_VALUE() and LAST_VALUE() return the first or last value in the window frame, and LAST_VALUE() commonly surprises developers because with the default frame it returns the current row's value rather than the true last row in the partition, unless the frame is explicitly extended to UNBOUNDED FOLLOWING.
Cricket analogy: LAG(Score, 1) OVER (PARTITION BY PlayerID ORDER BY MatchDate) pulls a batsman's previous innings score onto the current row, letting a broadcast graphic instantly show 'up from 34 last innings' without a manual self join.
LAST_VALUE() with the default window frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) returns the current row's own value, not the actual final row in the partition — you must explicitly write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the true last value across the whole partition.
- Window functions compute per-row results over a related set of rows without collapsing them, unlike GROUP BY.
- PARTITION BY divides rows into groups; ORDER BY within OVER() determines row sequence for ranking/running calculations.
- RANK() leaves gaps after ties, DENSE_RANK() does not, ROW_NUMBER() ignores ties entirely.
- Aggregate functions (SUM, AVG, MIN, MAX) can run as window functions via OVER() for running totals.
- The frame clause (ROWS/RANGE BETWEEN) controls exactly which rows are included in the window calculation.
- LAG()/LEAD() access prior/following row values within a partition without a self join.
- LAST_VALUE() needs an explicit ROWS BETWEEN ... AND UNBOUNDED FOLLOWING frame to return the true last row.
Practice what you learned
1. What is the key difference between GROUP BY and a window function (OVER clause)?
2. If two rows tie for rank 2 using RANK(), what rank does the next distinct row receive?
3. What does LAG(TotalAmount, 1) OVER (PARTITION BY CustomerID ORDER BY OrderDate) return for a customer's very first order?
4. Why does LAST_VALUE() with the default window frame often return an unexpected result?
5. Which window function assigns a strictly increasing integer to every row, ignoring ties entirely?
Was this page helpful?
You May Also Like
Aggregations and Grouping
Master aggregate functions and GROUP BY/HAVING to summarize rows into meaningful totals, averages, and counts in SQL Server.
Subqueries and CTEs
Learn how to nest queries and use Common Table Expressions to break complex T-SQL logic into readable, reusable, and recursive building blocks.
Joins in T-SQL
Understand how INNER, OUTER, CROSS, and self joins combine rows from multiple tables in SQL Server, and how to avoid common join pitfalls.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics