SQL Server window functions: ROW_NUMBER, RANK, LAG, running totals and the RANGE trap

A window function computes a value for each row from a set of related rows (its window) without collapsing them the way GROUP BY does. You keep every order, and next to each one you can show its customer’s order count, its rank by value, the previous order’s date, or a running total.

The OVER clause defines the window:

PARTITION BY splits the rows into groups (like GROUP BY, but without merging them).
ORDER BY orders the rows inside each group.
A frame (ROWS or RANGE) limits which of those rows count, for running totals and moving averages.

The examples use the SpDevDemo database.

Numbering and ranking

Rank customers by their number of orders. Nine customers have 10 orders and almost all the rest have 9, which shows how the three functions treat ties:

WITH counts AS (
    SELECT CustomerId, COUNT(*) AS Orders
    FROM dbo.Orders
    GROUP BY CustomerId
)
SELECT TOP (12)
    CustomerId,
    Orders,
    ROW_NUMBER() OVER (ORDER BY Orders DESC, CustomerId) AS row_num,
    RANK()       OVER (ORDER BY Orders DESC)             AS rnk,
    DENSE_RANK() OVER (ORDER BY Orders DESC)             AS dense_rnk
FROM counts
ORDER BY row_num;

CustomerId  Orders  row_num  rnk  dense_rnk
----------  ------  -------  ---  ---------
1           20000   1        1    1
3357        10      2        2    2
3760        10      3        2    2
...         10      ...      2    2
19598       10      10       2    2
2           9       11       11   3
3           9       12       11   3

ROW_NUMBER() numbers rows 1, 2, 3… with no ties: equal values get different numbers, in no guaranteed order unless the ORDER BY is unique.
RANK() gives ties the same number and leaves a gap after them: 1, 2, 2, 4.
DENSE_RANK() gives ties the same number without gaps: 1, 2, 2, 3.
NTILE(4) splits each partition into 4 roughly equal buckets (quartiles).

Top N per group

The most common use: the latest order of each customer, or the three biggest. Number the rows in a CTE, then filter on the number:

WITH ranked AS (
    SELECT CustomerId, OrderId, OrderDate, Total,
           ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC, OrderId DESC) AS rn
    FROM dbo.Orders
)
SELECT CustomerId, OrderId, OrderDate, Total
FROM ranked
WHERE rn = 1
  AND CustomerId <= 5;

You can’t filter on a window function in the same query’s WHERE, because WHERE is evaluated before the window functions are; hence the CTE. Add a unique column (OrderId) to the ORDER BY so that “latest” is well defined when two orders share a date. For a few groups with an index that supports it, CROSS APPLY with TOP is often faster.

Previous and next rows: LAG and LEAD

SELECT TOP (5)
    OrderId,
    OrderDate,
    LAG(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId) AS previous_order,
    DATEDIFF(DAY,
             LAG(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId),
             OrderDate) AS days_since_previous
FROM dbo.Orders
WHERE CustomerId = 42
ORDER BY OrderDate;

LAG(col, n, default) reads the value n rows back (1 by default) and returns default (NULL by default) when there is none. LEAD reads forward. They replace self-joins on “the previous row”, which were slow and easy to get wrong.

Aggregates over a window

Any aggregate can take an OVER clause:

SELECT TOP (5)
    OrderId,
    CustomerId,
    Total,
    SUM(Total)   OVER (PARTITION BY CustomerId)               AS customer_total,
    COUNT(*)     OVER (PARTITION BY CustomerId)               AS customer_orders,
    CAST(100.0 * Total / SUM(Total) OVER (PARTITION BY CustomerId) AS DECIMAL(5, 2)) AS pct_of_customer
FROM dbo.Orders
WHERE CustomerId = 42;

Without ORDER BY, the window is the whole partition: every row sees the same total.

Running totals, and the RANGE trap

Add ORDER BY to an aggregate’s OVER clause and it becomes cumulative:

SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate)

What most people don’t know is that this uses a default frame: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE means “every row whose ORDER BY value is less than or equal to the current one”. When two rows have the same OrderDate, both get the total including each other. A running total with ROWS counts strictly row by row.

On the demo data, where customer 1 has several orders on most days, the two give different answers:

SELECT OrderId,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate) AS running_range
INTO #range FROM dbo.Orders;

SELECT OrderId,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate ROWS UNBOUNDED PRECEDING) AS running_rows
INTO #rows FROM dbo.Orders;

SELECT COUNT(*) AS rows_that_differ
FROM #range AS a JOIN #rows AS b ON b.OrderId = a.OrderId
WHERE a.running_range <> b.running_rows;

rows_that_differ
----------------
18599

Neither is wrong; they answer different questions. But the default is rarely the one you meant. Write the frame explicitly, and make the ORDER BY unique (ORDER BY OrderDate, OrderId) so that ROWS gives the same result every time.

It’s also a performance question. In row mode, a RANGE frame is processed with an on-disk worktable, while ROWS uses an in-memory spool. Forcing row mode on SQL Server 2022, with a unique ORDER BY so both return the same numbers:

SET STATISTICS IO, TIME ON;

SELECT CustomerId, OrderId,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId) AS running_total
INTO #r1 FROM dbo.Orders
OPTION (USE HINT('DISALLOW_BATCH_MODE'));

SELECT CustomerId, OrderId,
       SUM(Total) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId ROWS UNBOUNDED PRECEDING) AS running_total
INTO #r2 FROM dbo.Orders
OPTION (USE HINT('DISALLOW_BATCH_MODE'));

SET STATISTICS IO, TIME OFF;

-- RANGE (the default)
Table 'Worktable'. Scan count 220000, logical reads 1200525
 SQL Server Execution Times: CPU time = 4489 ms,  elapsed time = 775 ms.
-- ROWS
Table 'Worktable'. Scan count 0, logical reads 0
 SQL Server Execution Times: CPU time = 905 ms,  elapsed time = 190 ms.

1.2 million worktable reads and five times the CPU, for the same result. On SQL Server 2019 and later, the optimizer can use batch mode on rowstore tables (on Enterprise and Developer editions, at compatibility level 150 or higher), whose Window Aggregate operator avoids the worktable either way. Don’t count on it: Standard edition, older compatibility levels and many plans still run in row mode.

LAST_VALUE surprises

The same default frame explains why LAST_VALUE seems broken:

SELECT TOP (3)
    OrderId,
    LAST_VALUE(OrderId) OVER (ORDER BY OrderDate, OrderId) AS last_default,
    LAST_VALUE(OrderId) OVER (ORDER BY OrderDate, OrderId
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_real
FROM dbo.Orders
WHERE CustomerId = 42
ORDER BY OrderDate, OrderId;

OrderId  last_default  last_real
-------  ------------  ---------
19403    19403         199394
39402    39402         199394
59401    59401         199394

With the default frame, the window ends at the current row, so the “last value” is the current row’s own value. Give it the whole partition, or use FIRST_VALUE with the order reversed.

Moving averages

A frame of a fixed size gives moving windows:

SELECT TOP (5)
    OrderDate,
    Total,
    AVG(Total) OVER (ORDER BY OrderDate, OrderId ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM dbo.Orders
WHERE CustomerId = 1
ORDER BY OrderDate, OrderId;

Indexes for window functions

A window function needs its rows sorted by PARTITION BY then ORDER BY. An index with those columns as its key, in that order and including the other columns used, lets SQL Server read them already sorted and skip the Sort operator. The index on (CustomerId, OrderDate) with Total included serves every example on this page.

SpDev Pro’s profiler shows the cost of each statement in a procedure, so a running total that suddenly takes most of the time stands out; the captured plan then shows whether it’s the sort, the worktable or a spill to tempdb.