GREATEST ignoring NULL?

Pieter_Sheth-Vo

Is there a function that works like GREATEST that returns the greatest non-null value?

GREATEST ( 1, 0, 1, 1, 0, 0, null, 1, 0) => 1
GREATEST ( 0, 0, null, 0, 0) => 0
GREATEST ( null, null, null, null ) => null

My use case is easily getting the max of a potentially large number of columns for all rows where the columns are not all null e.g.

SELECT GREATEST (a1, a2, ..., a1024) AS a
FROM table
WHERE GREATEST (a1, a2, ..., a1024)  IS NOT NULL

(Here I'm using a1 as a simple version but in practice each could be an expression).

GREATEST doesn't work becuase it returns NULL if any are null, GREATEST(1,1,null, 0,1) => NULL
COALESCE won't work because it'll return the first non-null value, not the greatest e.g. coalesce(null,0,1); => 0

mosheg

For your original use case with many columns, you could write:

SELECT MAX(value)
FROM (
    SELECT EXPLODE(ARRAY[a1, a2, ..., a1024]) OVER()
    FROM table
) foo;

Or alternatively:

SELECT EXPLODE(ARRAY[a1, a2, ..., a1024]) OVER()
FROM table
ORDER BY value DESC
LIMIT 1;

OR

select max(value) from (SELECT EXPLODE(ARRAY[1, 0, 2, 3, 3, 77, null, 1, 0]) OVER()) foo;
 max
-----
  77
(1 row)

Pieter_Sheth-Vo

Could we create a custom Vertica function GREATEST_CUSTOM function that behaves like below? Is there a good example to follow?

GREATEST_CUSTOM( 0, 1, NULL, 3, NULL, 2) => 3
GREATEST_CUSTOM( NULL, NULL, NULL, NULL) => NULL

Bryan_H

You could create a polymorphic UDx to accept any number of inputs and return a scalar output, as described here with examples:
https://docs.vertica.com/25.3.x/en/extending/developing-udxs/arguments-and-return-values/creating-polymorphic-udx/