Skip to main content

Documentation Index

Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt

Use this file to discover all available pages before exploring further.

Returns a single arbitrary value from the specified column.

​
Syntax

ANY_VALUE(<expression>) [FILTER ([WHERE] <condition>)]

​
Parameters

ParameterDescriptionSupported input types
<expression>Any expressionAny
<condition>An optional boolean expression to filter rows used in aggregationBOOL
This function ignores NULL inputs. It returns NULL only when all inputs are NULL or there are no inputs.

​
Return Type

Same as input type

​
Examples

Example 
SELECT
	ANY_VALUE(nickname)
FROM
	UNNEST (ARRAY['kennethpark', NULL, 'sabrina21', 'ruthgill', 'steven70']) AS players(nickname);
Returns Any value of the nickname column, excluding NULL. The first time the query below runs, the nickname kennethpark might be returned. The second time the query runs, sabrina21 or any other value, such as ruthgill or steven70, might be returned, but NULL will never be returned while non-NULL options exist. Example 
SELECT ANY_VALUE(data) FROM UNNEST (ARRAY[NULL, NULL, NULL]) arr(data);
SELECT ANY_VALUE(data) FROM UNNEST (ARRAY[1,2,3]) arr(data) WHERE false;
Returns NULL as no non-NULL values are available.