last, last_value

On this page

Returns the last value in a group. last_value skips NULL values.

Syntax

last(<expr>)
last_value(<expr>)

Arguments

ArgumentTypeDescription
<expr>Any typeInput value.

Returns

Returns the same type as the input.

  • last returns the last value, even if it is NULL.
  • last_value returns the last 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 last campaign value with the last 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
    last(campaign) AS latest_campaign,
    last_value(campaign) AS latest_known_campaign
+------------+-----------------+-----------------------+
| session_id | latest_campaign | latest_known_campaign |
+------------+-----------------+-----------------------+
| s-17       | NULL            | loyalty               |
+------------+-----------------+-----------------------+

last preserves the missing value on the latest event; last_value skips it.