I want to nest max functions in bigquery.
Sample-
Max(a,Max(b,Max(c,d)))
It is giving an error of invalid arguments. So, what I tried is the Greatest function
Greatest(a,b,c,d). But the issue with Greatest function is that, it will return null if any of the values is null. But What I wanted is that, It should return Greatest value even if any of the values is null.
For Example-
Greatest(1,3,4,null)- It should return 4
Greatest(null,null,null)- It should return null
You could use COALESCE() along with GREATEST():
SELECT GREATEST(
COALESCE(a, b, c, d),
COALESCE(b, a, c, d),
COALESCE(c, a, b, d),
COALESCE(d, a, b, c))
FROM yourTable;
The above logic behaves such that each of the four values being passed to GREATEST() "defers" to some other value in the event that it is NULL. This way, the call to GREATEST() will only ever return NULL in the event that all four inputs are simultaneously null.
You may consider below scalar subquery since MAX aggregate function
Returns the maximum value of non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Returns NaN if the input contains a NaN.
WITH sample_data AS (
SELECT 1 a, 3 b, 4 c, null d UNION ALL
SELECT null, null, null, null
)
SELECT (SELECT MAX(n) FROM UNNEST([a, b, c, d]) n) AS max FROM sample_data;
How about below approach?
create temp function maxx(x array<int64>)
returns float64 language js as r"""
return Math.max.apply(null, x);
""";
select *, maxx([a,b,c,d]) as max_value
from your_table
with output like below
