Nested Max function in SQL-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

You may consider below scalar subquery since