The Four Window Functions You Will Actually Use

SQL window functions calculate across related rows without collapsing them into one grouped result. Four patterns cover many everyday needs: numbering, ranking, previous values, and running totals.

Understand the Window Before the Function

OVER defines the rows and ordering relevant to a window calculation. PARTITION BY divides those rows into separate groups for the calculation. Unlike GROUP BY, it usually leaves the original detail rows in the result.

The ORDER BY inside OVER controls the calculation's sequence. The final ORDER BY controls how the result is displayed. One does not replace the other.

CREATE TABLE #WindowSales
(
    SaleId int PRIMARY KEY,
    CustomerId int NOT NULL,
    SaleDate date NOT NULL,
    Amount decimal(10,2) NOT NULL
);
INSERT #WindowSales VALUES
(1,10,'20260920',100),
(2,10,'20260921',150),
(3,10,'20260921',150),
(4,20,'20260920',80),
(5,20,'20260922',120);

Run the following examples in the same session as this temporary table. The repeated date and amount are intentional. They make ordering ties visible instead of allowing a perfectly tidy dataset to hide them.

Number Rows With ROW_NUMBER

SELECT SaleId, CustomerId, SaleDate, Amount,
       ROW_NUMBER() OVER
       (PARTITION BY CustomerId ORDER BY SaleDate, SaleId) AS sale_number
FROM #WindowSales
ORDER BY CustomerId, SaleDate, SaleId;

ROW_NUMBER assigns a sequence within each customer partition. The unique SaleId breaks ties between sales on the same date. Without a complete ordering, tied rows can receive different sequence numbers across executions.

Use this pattern for selecting a latest record, numbering detail rows, or choosing a row according to a defined rule. To filter by the calculated number, place the query in a CTE or derived table. The alias is not available in the same SELECT's WHERE clause.

The numbering exists for the result being calculated. It is not a stored identifier and should not replace the table's key. Changes to the selected population can change the assigned numbers.

Preserve Ties With RANK

SELECT SaleId, CustomerId, Amount,
       RANK() OVER
       (PARTITION BY CustomerId ORDER BY Amount DESC) AS amount_rank
FROM #WindowSales
ORDER BY CustomerId, amount_rank, SaleId;

RANK gives equal ordering values the same rank and leaves gaps afterward. That differs from ROW_NUMBER, which assigns a different number to each row. Choose according to the business meaning of a tie.

Do not add SaleId inside this ranking window merely to make every ordering key unique. Doing so would remove ties between equal amounts. The final display can use SaleId without changing the intended ranking rule.

A request for the top three ranks can return more than three rows. A request for exactly three rows needs a different tie policy. Clarify that requirement before selecting the function.

Look Back With LAG

SELECT SaleId, CustomerId, SaleDate, Amount,
       LAG(Amount) OVER
       (PARTITION BY CustomerId ORDER BY SaleDate, SaleId) AS previous_amount,
       Amount - LAG(Amount) OVER
       (PARTITION BY CustomerId ORDER BY SaleDate, SaleId) AS amount_change
FROM #WindowSales
ORDER BY CustomerId, SaleDate, SaleId;

LAG reads a value from a preceding row in the defined sequence. The first row has no preceding row, so the default result is NULL. That can be more meaningful than pretending the previous amount was zero.

Previous row does not necessarily mean previous calendar day. Missing dates and multiple rows per day still follow the chosen ordering. Aggregate to the intended daily grain first when the question is about daily changes.

Filtering also affects which rows are available to the window calculation. If you need a previous value from outside the displayed period, calculate over the wider population first. Apply the final display filter afterward.

Build a Running Total With SUM

SELECT SaleId, CustomerId, SaleDate, Amount,
       SUM(Amount) OVER
       (PARTITION BY CustomerId ORDER BY SaleDate, SaleId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_amount
FROM #WindowSales
ORDER BY CustomerId, SaleDate, SaleId;

The explicit ROWS frame accumulates from the partition's first row through the current row. It makes the intended row-by-row calculation visible. A default RANGE frame can treat ordering peers together when ties exist.

Use a unique ordering when the running total must advance through individual transactions predictably. For a daily cumulative total, aggregate each day before applying the window. The correct frame cannot repair the wrong grain.

Keep Correctness and Performance Together

Window calculations can require sorting and substantial memory for large inputs. Suitable indexes may help, but inspect the actual plan and representative workload. Replacing a cursor with a window expression does not remove the need to measure.

Test empty groups, ties, missing dates, and boundary filters. Explain the partition, order, and frame in ordinary language before accepting the query. Those choices define the answer more than the function name does.

A window function is not just shorter syntax, it is a precise rule for relating each row to others.

This post was rewritten from scratch in September 2026. The original, published on 2013-08-05, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.