Ignore NULL values in GREATEST and LEAST functions

[Julie]

Hi guys,

Can anyone think of a way to get the greatest and least functions to ignore NULLs?

That is, these examples return NULL:

Code: Select all

dbadmin=> SELECT greatest('A', 'B', 'C', NULL);
greatest
----------

(1 row)

dbadmin=> SELECT least('A', 'B', 'C', NULL);
least
-------

(1 row)

I'd like to ignore the NULL values and get these results if possible:

Code: Select all

dbadmin=> SELECT greatest('A', 'B', 'C', NULL);
greatest
----------
C
(1 row)

dbadmin=> SELECT least('A', 'B', 'C', NULL);
least
-------
A
(1 row)

Thanks!

[id10t]

HI Julie!

Only by filtering NULLs you can solve it. No exists such data type char/varchar/number/date that a greatest and a lowest value in the same time, so no casting as option - only filtering, i.e. ignorance in selection not in function.

[harryrundles]

Here's a suggestion...

On columns where you know there could be NULLS that you want to ignore, use the NVL function to replace the NULL with an appropriate value that can't be the greatest or the least.

Example for text:

Code: Select all

dbadmin=> SELECT greatest('A', 'B', 'C', NVL(NULL, CHR(0)));
 greatest
----------
 C
(1 row)

dbadmin=> SELECT least('A', 'B', 'C', NVL(NULL, CHR(177)));
 least
-------
 A
(1 row)

Example for numbers:

Code: Select all

dbadmin=> SELECT greatest(1, 2, 3, NVL(NULL, -9999999999999999999));
 greatest
----------
        3
(1 row)

dbadmin=> SELECT least(1, 2, 3, NVL(NULL, 9999999999999999999));
 least
-------
     1
(1 row)

So as skwa indicated my suggestion is kind of filtering out NULLS...

[Julie]

Thanks, guys! Great answers!