ARRAY_MAX¶

Syntax¶

ARRAY_MAX( <array> )

Arguments¶

array

The input ARRAY.

Returns¶

This function returns a VARIANT that contains the element with the highest value that is not a SQL NULL.

The function returns NULL if array is NULL, empty, or contains only SQL NULL elements.

Usage Notes¶

A SQL NULL is distinct from an explicit null value in semi-structured data (for example, a JSON null in JSON data). Explicit null values are considered when identifying the element with the highest value.

Examples¶

The following example returns a VARIANT containing the element with the highest value in an ARRAY constant:

SELECT ARRAY_MAX([20, 0, NULL, 10, NULL]);

+------------------------------------+
| ARRAY_MAX([20, 0, NULL, 10, NULL]) |
|------------------------------------|
| 20                                 |
+------------------------------------+

The following example demonstrates that a JSON null is handled differently than a SQL NULL. If array contains a JSON null, the function returns the JSON null.

SELECT ARRAY_MAX([NULL, PARSE_JSON('null'), NULL]);

+--------------------------------------------------+
| ARRAY_MAX([20, 0, PARSE_JSON('NULL'), 10, NULL]) |
|--------------------------------------------------|
| null                                             |
+--------------------------------------------------+

The following example demonstrates that the function returns NULL if the input ARRAY is empty:

SELECT ARRAY_MAX([]);

+---------------+
| ARRAY_MAX([]) |
|---------------|
| NULL          |
+---------------+

The following example demonstrates that the function returns NULL if the input ARRAY contains only SQL NULLs:

SELECT ARRAY_MAX([NULL, NULL, NULL]);

+-------------------------+
| ARRAY_MAX([NULL, NULL]) |
|-------------------------|
| NULL                    |
+-------------------------+