Skip to main content
Question

GREATEST ignoring NULL?

  • February 12, 2025
  • 3 replies
  • 5 views
Pieter_Sheth-Vo
Forum|alt.badge.img+2

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

    3 replies

    mosheg
    Forum|alt.badge.img+2
    • mosheg
    • Participating Frequently
    • February 12, 2025

    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
    Forum|alt.badge.img+2
    • Pieter_Sheth-Vo
    • Author
    • Participating Frequently
    • August 6, 2025

    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
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • August 20, 2025

    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/

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement