ARBITRARY

​

Syntax

​

Aggregate

arbitrary([DISTINCT] value)

​

Window over all rows in each group

arbitrary(value) OVER ([PARTITION BY key] ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

• Use the full-group window shown above. Other frame boundaries, ordering options, and NULL-handling clauses are outside the forms covered on this page.

​

Arguments

​

Aggregate and full-group window

​

Returns

• Type: same SQL type as value
• NULL behavior: A selected value can be NULL; it is not guaranteed to select a non-NULL row.
• Empty input: Returns NULL on empty input.

​

Properties

​

Usage notes

• A NULL value can be selected, even when the group also contains non-NULL values.
• INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
• Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
• Deterministic: The selected value can change if the input order changes.
• DISTINCT has no effect and does not make the function skip NULL values.

​

Examples

​

Choose a value within each group

WITH t(g, v) AS (VALUES ('a', 7), ('a', 9), ('b', 10), ('b', 20)) SELECT g, arbitrary(v) AS result FROM t GROUP BY g ORDER BY g NULLS LAST;

​

Keep every row with a full-group window

WITH t(id, g, v) AS (VALUES (1, 'a', 7), (2, 'a', 9), (3, 'b', 10), (4, 'b', 20)) SELECT id, g, v, arbitrary(v) OVER (PARTITION BY g ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS chosen FROM t ORDER BY id NULLS LAST;

​

A group containing only NULL

WITH t(v) AS (VALUES (CAST(NULL AS INTEGER))) SELECT arbitrary(v) AS result FROM t;

​

Limitations

• The result can be NULL even when other values are non-NULL. Adding DISTINCT does not make the function skip NULL values.