LAG IGNORE NULLS: Read the Previous Available Value

LAG IGNORE NULLS can read the previous available value rather than the immediately previous row. I keep the ordering and default rule explicit because those choices answer different questions.

Decide which previous value is needed

An event sequence can contain rows without a measurement. The previous row still exists, even when its measurement is NULL. Reading that row differs from searching backward for the previous available measurement. The query must state which meaning the consumer requires.

RESPECT NULLS is the ordinary LAG behavior. IGNORE NULLS skips NULL values during the backward lookup. That option is available in SQL Server 2022 and later.

I’d verify the supporting engine and update before adopting the option. This script doesn’t install an update or claim an older build behaves identically. Its results come from the SQL Server 2025 build named below. Version readiness belongs beside the query’s semantic contract.

Give the window a complete sequence

The input has two partitions. The first contains six positions, including three missing measurements between or after available values. The second contains two missing measurements only. Both outputs order by a unique sequence within each partition.

The query asks for an offset of one and supplies negative one as the default. It compares RESPECT NULLS with IGNORE NULLS beside the original measurement. Every row remains in the result. Skipping a value during lookup doesn’t remove the row itself.

The sequence is unique within each partition in this small input. That avoids an ambiguous previous row among tied ordering values. A real event stream may need a separate unique identifier. I’d include it in the window order when timestamps can tie.

WITH Inputs AS
(
    SELECT GroupId, Seq, Measurement
    FROM (VALUES (1, 1, NULL), (1, 2, 10), (1, 3, NULL),
                 (1, 4, NULL), (1, 5, 20), (1, 6, NULL),
                 (2, 1, NULL), (2, 2, NULL))
        AS v(GroupId, Seq, Measurement)
)
SELECT GroupId, Seq, Measurement,
       LAG(Measurement, 1, -1) RESPECT NULLS
           OVER (PARTITION BY GroupId ORDER BY Seq) AS PreviousRowValue,
       LAG(Measurement, 1, -1) IGNORE NULLS
           OVER (PARTITION BY GroupId ORDER BY Seq) AS PreviousAvailableValue
FROM Inputs
ORDER BY GroupId, Seq;

Read the default as a lookup outcome

At the first position of each partition, both lookups return the supplied default. The tested SQL Server 2025 build 17.0.5005.3 returns NULL at position two through both lookups. No earlier non-NULL measurement exists there. That measured result is NULL, not the supplied default of negative one.

At the fourth and fifth positions, the previous row contains NULL. RESPECT NULLS returns NULL again. IGNORE NULLS reaches the earlier value ten. Its offset counts available values under this rule, rather than counting every row in the partition.

The all-missing partition makes the measured distinction clear. Both first-row results are negative one, while both second-row results are NULL. The default applies when the offset reaches beyond the partition. I wouldn’t extend that into an unverified all-NULL fallback guarantee.

Keep measurement rules separate

A previous available measurement isn’t necessarily a valid current estimate. The lookup doesn’t limit how old the measurement can be. It also doesn’t establish whether carrying it forward is appropriate. Those decisions need their own time and business rules.

I can justify filling a missing display value from the previous available value. That would require another expression using this lookup result. It changes the presentation contract. The demonstration retains the original NULL instead of quietly overwriting its meaning.

The default is a real integer value in this example. An application might choose typed NULL or another documented marker instead. A marker that can also be a real measurement needs a separate flag. Don’t make one value carry both meanings invisibly.

Compare the complete partitions

The script reads a literal VALUES list through a CTE. It creates no objects or connection settings. The final ORDER BY uses partition and sequence columns. Both original measurements and lookup outputs remain visible for all eight rows.

The query returns all eight tuples measured on the stated SQL Server 2025 build. Compare both partition boundaries and each NULL/default result on your supporting version. A successful backward lookup doesn’t prove every missing-input fallback. Keep a separately required fallback policy explicit.

Say which previous you mean, and the query will say it back.

A previous available value is not the previous row, it is an ordered lookup.

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