Least value but not NULL in Oracle SQL

If any argument is NULL, you want to take the least of the other argument(s). If all arguments are NULL, you want to return NULL.

I might use something like this for two arguments:

LEAST(NVL(colA,colB), NVL(colB,colA))

It starts getting ugly for >2 arguments though:

LEAST(COALESCE(colA,colB,colC)
     ,COALESCE(colB,colA,colC)
     ,COALESCE(colC,colA,colB))

At which point I'd start considering magic values; but this can be buggy (e.g. what if one of the values legitimately is the magic value?):

SELECT CASE WHEN r = maxv THEN NULL ELSE r END AS result
FROM   (SELECT LEAST(NVL(:colA,maxv)
                    ,NVL(:colB,maxv)
                    ,NVL(:colC,maxv)) AS r, maxv
        FROM   (SELECT 9.999999999999999999999999999999999999999e125
                       AS maxv FROM DUAL));

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

I doubt that's actually your query. Maybe you're doing something more like this?

select least(some_column) from dual

If so, change it to this:

select least(some_column) from dual where some_column is not null

---

Or, if you're doing something more like this, where you can't just use where to filter the set,

select least(expr1,expr2,expr3) from dual

do this:

select least(coalesce(expr1, 12345), coalesce(expr2, 12345), coalesce(expr3, 12345)) from dual

Where 12345 is a value big enough that it would only be chosen if all other expressions are NULL.

Using the three expressions in three orders in three 'coalesce' calls and putting these in the 'least' call will result the least of three, ignoring null unless all three expressions are null.

select least(coalesce(expr1, expr2, expr3), coalesce(expr2, expr3, expr1), coalesce(expr3, expr1, expr2)) from dual