FIRST_VALUE IGNORE NULLS: Find the First Available Value

FIRST_VALUE IGNORE NULLS finds the first present value within the selected ordered frame. I keep the frame explicit when availability changes as rows arrive. Ignoring a missing value does not remove its source row.

Start with a missing first observation

The example supplies four readings in unique RowId order. The first reading is NULL, followed by ten, another NULL and twenty. That sequence makes the missing first observation part of the test.

Both expressions use a frame from the first row through the current row. The first row therefore cannot see a later reading. This is an availability-through-current-position question rather than a look-ahead question.

FirstRespectingNull explicitly uses RESPECT NULLS. FirstAvailable uses IGNORE NULLS with the same ordering and frame. The NULL option is the deliberately changed part of these two analytic expressions.

I retain RowId and Reading in the output. Each source row remains visible beside its selected first value. The function does not filter out the missing rows from the returned rowset.

WITH Readings AS
(
    SELECT RowId,Reading
    FROM (VALUES (1,CAST(NULL AS int)),(2,10),(3,CAST(NULL AS int)),(4,20)) AS v(RowId,Reading)
)
SELECT RowId,Reading,
    FIRST_VALUE(Reading) RESPECT NULLS OVER (ORDER BY RowId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstRespectingNull,
    FIRST_VALUE(Reading) IGNORE NULLS OVER (ORDER BY RowId
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstAvailable
FROM Readings
ORDER BY RowId;

Read the first available value over time

At row one, both expected results are NULL. The frame contains only the missing reading. Ignoring that value cannot invent a present reading that does not yet belong to the frame.

At row two, FirstAvailable is expected to return ten. A present reading now exists within the selected frame. FirstRespectingNull remains NULL because its first ordered value is still missing.

At row three, FirstAvailable remains ten even though the current reading is NULL. The expression looks for the first present value in the frame. It is not merely a test of the current source cell.

At row four, FirstAvailable still returns ten rather than twenty. Twenty is later, while ten remains the first present reading. This is different from choosing the most recent available value.

Keep first, latest and minimum as separate questions

The first present value follows the selected order. It need not be the smallest numeric reading. A later smaller amount would not retroactively become the first observation.

Likewise, the function does not implement last-known-value filling. A report needing the latest present reading must define that different operation. Reusing the first available value would preserve an outdated source indefinitely.

I’d include a later value different from the first in any test. The twenty in this example exposes that distinction. A sequence containing only ten and NULL could make first and latest appear interchangeable.

The source ordering also needs a complete rule. This example uses unique RowId values. A real timestamp with ties needs a deliberate tie-breaker if the identity of the first reading matters.

Choose the frame and missing-data policy together

A frame covering future rows would answer another availability question. It could find a later present reading even for the first source row. That may be appropriate for a retrospective report but not a current-position interpretation.

The IGNORE NULLS syntax requires SQL Server 2022 or later. RESPECT NULLS is the default behavior. I name both options here so the comparison remains readable without relying on that default.

Partitioning can establish a separate sequence for each device or account. Ignoring a missing value does not authorize borrowing a reading from another partition. Keep the partition contract beside the ordering rule.

The query reads only made-up int values and changes no settings. Its expected output is four complete rows, including both missing source rows. No data-quality repair or measurement accuracy claim is attached to the selected values.

When adapting the example, preserve a missing first row, a first present value and a later different value. Check the current frame before applying a label such as first available. That makes the source population and the selection policy explicit.

Small examples like this make a window frame easy to see.

The first available reading is not the latest reading, it is the earliest present value.

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