Least value but not NULL in Oracle SQL

Question

I wanted to use function LEAST in my procedure to find the smallest value. The problem is that some of the values might have been NULLs so if I do

select least(NULL,0,1) from dual

The answer I get is NULL, which is probably correct by is not something I am expecting to return. I would like to get the least real non zero value. Any help greatly appreciated.

Answer 1

Lowest value from column 1 to N:

SELECT
    LEAST(COALESCE(Col1, BINARY_DOUBLE_INFINITY),
          COALESCE(Col2, BINARY_DOUBLE_INFINITY),
          ... ,
          COALESCE(ColN, BINARY_DOUBLE_INFINITY)
    )
FROM MY_TABLE

Greatest value from column 1 to N:

SELECT
    GREATEST(
        COALESCE(Col1, -BINARY_DOUBLE_INFINITY),
        COALESCE(Col2, -BINARY_DOUBLE_INFINITY),
        ..., 
        COALESCE(ColN, -BINARY_DOUBLE_INFINITY)
    )
FROM MY_TABLE

To remove the infinity results from the query, just add the where clause checking if all the values are null. You can do that using the coalesce function as well:

WHERE COALESCE(Col1, Col2, ..., ColN) IS NOT NULL