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

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));

Answer 2

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

Answer 3

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

Answer 4

One way is to define own version of LEAST/GREATEST func:

-- here version for two params
WITH  FUNCTION least_improved(p1 IN NUMBER, p2 IN NUMBER) RETURN NUMBER IS
BEGIN
    IF p1 IS NULL THEN
       RETURN p2;
    ELSIF p2 IS NULL THEN
       RETURN p1;
    ELSE
       RETURN LEAST(p1, p2);
    END IF; 
END;
SELECT least_improved(col1, col2)
FROM table_name;
/

Answer 5

If you have a lot of columns from which you want the min & max, you can UNPIVOT them into a single column, which then makes it simple; exclude NULLS from your single column & use a MIN() or MAX() to get what you want.

I needed to do this with about 50 date columns (not a great db design) and UNPIVOT seemed to be the most sensible / practical way of doing it

Answer 6

SELECT least(1, 2, 3, nvl(NULL,4)) FROM dual

Return: 1

SELECT GREATEST(1, 2, 3, nvl(NULL,0)) FROM dual

Return: 3