SQL Server Window Functions: Get the Frame and Order Right

Window functions can attach a running balance, previous value, or ranking to each row without collapsing the result into one row per group. That makes them attractive for reporting, but a short expression can conceal a major business decision. What counts as the previous row? Do transactions on the same day share one balance? Does the calculation include records hidden by a report filter?

Treat partition, order, and frame as three separate choices. PARTITION BY identifies independent groups. ORDER BY inside OVER establishes the calculation's sequence. For functions that support it, the frame selects which rows within that partition contribute to the current result. The final query's ORDER BY controls presentation and does not substitute for any of these choices.

Make ties visible in a small example

This ledger contains two entries on the same date. That tie is intentional.

DECLARE @Ledger table (
    AccountId int, EntryId int, PostedOn date, Amount decimal(12,2)
);
INSERT @Ledger VALUES
(1, 1, '20250101', 100.00),
(1, 2, '20250101', -20.00),
(1, 3, '20250102', 50.00),
(2, 4, '20250101', 7.00);

SELECT AccountId, EntryId, Amount,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn
    ) AS DatePeerTotal,
    SUM(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS RunningBalance,
    LAST_VALUE(Amount) OVER (
        PARTITION BY AccountId ORDER BY PostedOn, EntryId
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS FinalEntryAmount
FROM @Ledger
ORDER BY AccountId, PostedOn, EntryId;

For account 1, DatePeerTotal returns 80, 80, and 130. With an ordered aggregate and no explicit frame, the default RANGE frame includes peers sharing the current ordering value. Both January 1 entries therefore see the total through the whole date.

RunningBalance returns 100, 80, and 130. The explicit ROWS frame accumulates entries in the unique sequence PostedOn, EntryId. Adding ROWS without a deterministic ordering would not explain which tied entry comes first. If the business needs a posting sequence distinct from the generated identifier, store that sequence and use it instead.

FinalEntryAmount returns 50 for every account 1 row. The frame deliberately reaches the end of the partition. LAST_VALUE with a frame ending at the current row often returns the current value or its last peer, which surprises readers who interpret the function name as "last value in the account".

The account 2 result is independent and equals 7. Forgetting PARTITION BY would mix separate accounts into one balance. A plausible grand total does not prove that the row-level results are correct.

Define what a filter removes

The following query chooses one latest entry per account. The descending identifier resolves equal dates explicitly.

;WITH Ranked AS (
    SELECT AccountId, EntryId, PostedOn, Amount,
        ROW_NUMBER() OVER (
            PARTITION BY AccountId
            ORDER BY PostedOn DESC, EntryId DESC
        ) AS rn
    FROM @Ledger
)
SELECT AccountId, EntryId, PostedOn, Amount
FROM Ranked
WHERE rn = 1
ORDER BY AccountId;

Filtering rn in the outer query is necessary because the window result is not available to WHERE at the same query level. An outer query is also useful when displaying only a date range while calculating a balance over earlier history.

For example, filtering the ledger to January 2 before computing the running sum produces 50 for account 1, not its full balance of 130. If the report needs opening balances, either compute over the required history before filtering the display or calculate a separate opening amount and add the in-period movement. The latter can reduce work, but both parts must use a consistent data view.

LAG means the previous row in the defined sequence, not necessarily the previous calendar day. Missing dates do not create rows automatically. A true daily comparison may require aggregation to one row per day and a calendar table to represent days with no activity.

Match the execution cost to the question

An index beginning with the partition keys and then the ordering keys can reduce sorting for a compatible access path. Include only the additional columns justified by the workload. Different windows with incompatible orderings may still require multiple sorts.

Inspect actual row counts, sort spills, memory grants, and the number of rows entering the window operators. A small displayed result can require substantial historical processing. Avoid assuming that TOP in the outer query makes a full-partition calculation cheap.

Tests should include duplicate timestamps, multiple accounts, negative entries, a one-row account, and a display range that starts after the first transaction. If Amount permits NULL, define whether an unknown amount should be excluded from an aggregate or treated as a data-quality error. The safest expression is the one whose sequence and contribution rules another developer can explain from the SQL itself.

Technical references: Microsoft Learn: OVER clause · Microsoft Learn: ROW_NUMBER · Microsoft Learn: LAST_VALUE.