first_value

Selects the first encountered value, similar to any, but could accept NULL. Mostly it should be used with Window Functions. Without Window Functions the result will be random if the source stream is not ordered.

examples

CREATE TABLE test_data
(
    a Int64,
    b Nullable(Int64)
)
ENGINE = Memory;

INSERT INTO test_data (a, b) Values (1,null), (2,3), (4, 5), (6,null);

example1

The NULL value is ignored at default.

select first_value(b) from test_data;

┌─first_value_ignore_nulls(b)─┐
│                           3 │
└─────────────────────────────┘

example2

The NULL value is ignored.

select first_value(b) ignore nulls from test_data

┌─first_value_ignore_nulls(b)─┐
│                           3 │
└─────────────────────────────┘

example3

The NULL value is accepted.

select first_value(b) respect nulls from test_data

┌─first_value_respect_nulls(b)─┐
│                         ᴺᵁᴸᴸ │
└──────────────────────────────┘

example4

Stabilized result using the sub-query with ORDER BY.

SELECT
    first_value_respect_nulls(b),
    first_value(b)
FROM
(
    SELECT *
    FROM test_data
    ORDER BY a ASC
)

┌─first_value_respect_nulls(b)─┬─first_value(b)─┐
│                         ᴺᵁᴸᴸ │              3 │
└──────────────────────────────┴────────────────┘