Window Functions

Learn OVER(), ROW_NUMBER(), RANK(), and running totals with SUM() OVER (ORDER BY ...) in SQL Server.

Introduction

GROUP BY collapses rows into one row per group, which is great for totals but loses the individual rows. Window functions solve a different problem: they let you compute an aggregate or a ranking across a set of related rows while still returning every individual row. This makes them the tool of choice for running totals, rankings, and row numbering - all without subqueries or self-joins.

What You Will Learn

What a window function is and how it differs from GROUP BY.
The OVER() clause and its parts.
ROW_NUMBER() for unique row numbering.
RANK() and DENSE_RANK() for handling ties.
PARTITION BY and running totals with SUM() OVER (ORDER BY ...).

What Is a Window Function?

A window function performs a calculation across a 'window' of rows related to the current row - but unlike GROUP BY, it does not collapse those rows into fewer results. Every input row still appears in the output, now with an extra calculated column. Window functions are always used with an OVER() clause, which defines the window.

The OVER() Clause

OVER() can contain a PARTITION BY (to reset the calculation per group), an ORDER BY (to define row order within the window), and for some functions a frame clause. An empty OVER() with nothing inside treats the entire result set as one window.

SELECT
    OrderID,
    CustomerID,
    OrderTotal,
    SUM(OrderTotal) OVER () AS GrandTotal
FROM dbo.vwOrderSummary;

Result

Click Run to see what this code prints.

ROW_NUMBER()

ROW_NUMBER() assigns a unique, sequential integer to each row within its window, based on the ORDER BY inside OVER(). It is often used to find the 'top N per group' or to page through results.

SELECT
    OrderID,
    CustomerID,
    OrderTotal,
    ROW_NUMBER() OVER (ORDER BY OrderTotal DESC) AS RankByTotal
FROM dbo.vwOrderSummary;

Result

Click Run to see what this code prints.

RANK() and DENSE_RANK()

RANK() and DENSE_RANK() are similar to ROW_NUMBER(), but they handle ties differently: rows with equal ORDER BY values get the same rank. RANK() then leaves a gap in the numbering after a tie (1, 1, 3), while DENSE_RANK() does not (1, 1, 2).

SELECT
    EmployeeID,
    DepartmentID,
    Salary,
    RANK()       OVER (ORDER BY Salary DESC) AS SalaryRank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryDenseRank
FROM dbo.Employees;

Result

Click Run to see what this code prints.

PARTITION BY

PARTITION BY splits the rows into independent groups, restarting the window calculation for each one - similar in spirit to GROUP BY, but again without collapsing the rows. This example ranks employees by salary within their own department.

SELECT
    EmployeeID,
    DepartmentID,
    Salary,
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankInDept
FROM dbo.Employees;

Result

Click Run to see what this code prints.

Running Totals

Adding ORDER BY inside SUM() OVER (...) changes the window from 'the whole set' to 'every row up to and including this one,' producing a running total.

SELECT
    OrderID,
    OrderDate,
    OrderTotal,
    SUM(OrderTotal) OVER (ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS RunningTotal
FROM dbo.vwOrderSummary
ORDER BY OrderDate;

Result

Click Run to see what this code prints.

ROWS UNBOUNDED PRECEDING

This frame clause explicitly means 'from the first row of the window through the current row.' When you add ORDER BY inside OVER() without a frame clause, SQL Server defaults to this same behavior, but writing it explicitly makes the intent clear.

Common Mistakes

Avoid These Mistakes

Confusing window functions with GROUP BY and expecting rows to collapse - they do not.
Forgetting PARTITION BY when the calculation should reset per group, producing one grand total instead of per-group totals.
Using RANK() when ties should not skip numbers - use DENSE_RANK() instead.
Placing a window function's result directly in a WHERE clause - it must go in a subquery or CTE first, since WHERE is evaluated before window functions.
Not specifying ORDER BY inside OVER() when a running total is intended, which produces the same total for every row instead.

Best Practices

Use ROW_NUMBER() for strict, gap-free numbering; RANK()/DENSE_RANK() when ties should share a position.
Wrap a window function in a CTE when you need to filter on its result, since WHERE cannot reference it directly.
Be explicit with frame clauses (ROWS BETWEEN ...) when the default behavior might be ambiguous to a future reader.
Use PARTITION BY to keep per-group calculations independent instead of writing separate queries per group.
Index the columns used in PARTITION BY and ORDER BY within OVER() for large tables.

Frequently Asked Questions

Can I use a window function's result in a WHERE clause?

Not directly - WHERE is evaluated before window functions run. Wrap the query in a CTE or subquery, then filter in the outer query instead.

What is the difference between window functions and GROUP BY aggregates?

GROUP BY collapses each group into a single output row; window functions compute the aggregate but still return every original row alongside it.

Is ROW_NUMBER() guaranteed to be stable across runs?

Only if the ORDER BY inside OVER() is unique. If ties exist on the ordering column, the order among tied rows is not guaranteed unless you add a tiebreaker column.

Can I use multiple window functions in one query?

Yes, and each one can have its own OVER() clause with different PARTITION BY or ORDER BY as needed.

Key Takeaways

Window functions calculate across related rows without collapsing them, unlike GROUP BY.
OVER() defines the window: PARTITION BY groups it, ORDER BY sequences it.
ROW_NUMBER() gives unique sequential numbers; RANK() and DENSE_RANK() handle ties differently.
Adding ORDER BY inside a SUM() OVER() produces a running total.
Window function results cannot be filtered directly in WHERE - use a CTE or subquery.

Summary

Window functions unlock rankings, running totals, and per-group calculations without losing row-level detail, making them one of the most powerful tools in modern T-SQL. Next, you will look at dynamic SQL - building and running SQL statements at runtime, and the security precautions that come with it.

Next Lesson →

Dynamic SQL