first, first_value (aggregate function)

first, first_value

Returns the first value in a group. first_value skips NULL values.

Syntax

first(<expr>)
first_value(<expr>)

Arguments

Argument	Type	Description
`<expr>`	Any type	Input value.

Returns

Returns the same type as the input.

first returns the first value, even if it is NULL.
first_value returns the first non-NULL value, or NULL if all values are NULL.
Both return NULL for empty input.

Usage notes

Use WITHIN GROUP ORDER BY before AGGREGATE to define row order. Without an explicit order, the selected value can vary.

Examples

Compare the first campaign value with the first non-NULL campaign in a session:

VALUES
    ('s-17', 1, NULL::string), ('s-17', 2, 'spring'),
    ('s-17', 3, 'loyalty'), ('s-17', 4, NULL)
SELECT $0 AS session_id, $1 AS sequence, $2 AS campaign
GROUP BY session_id
WITHIN GROUP ORDER BY sequence
AGGREGATE
    first(campaign) AS earliest_campaign,
    first_value(campaign) AS earliest_known_campaign

+------------+-------------------+-------------------------+
| session_id | earliest_campaign | earliest_known_campaign |
+------------+-------------------+-------------------------+
| s-17       | NULL              | spring                  |
+------------+-------------------+-------------------------+

first preserves the missing value on the earliest event; first_value skips it.