Handling Null in Greatest Function in Oracle

Handling Null in Greatest function in Oracle

Use Oracle CASE... WHEN structure in your select:

SELECT COLA, COLB, CASE
  WHEN (COLA >= COLB OR COLB IS NULL)
    THEN COLA
  ELSE COLB
  END
  AS OUTPUT
FROM ...

Avoiding NULL return value with GREATEST function

Oracle Setup:

CREATE FUNCTION greatest_ignore_null(
  vals SYS.ODCINUMBERLIST
) RETURN NUMBER
IS
  output NUMBER := NULL;
BEGIN
  FOR i IN 1 .. vals.COUNT LOOP
    IF vals(i) IS NOT NULL AND ( output IS NULL OR vals(i) > output ) THEN
      output := vals(i);
    END IF;
  END LOOP;
  RETURN output;
END;
/

Query:

SELECT greatest_ignore_null(
         SYS.ODCINUMBERLIST( 1, NULL, 3, NULL, 2 )
       ) AS largest
FROM DUAL;

Output:

LARGEST
-------
      3

Least value but not NULL in Oracle SQL

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.

Oracle SQL - Select oldest of 3 dates, but ignore NULLs

Here is one way:

SELECT LEAST(COALESCE(DATE_1, DATE_2, DATE_3),
             COALESCE(DATE_2, DATE_1, DATE_3),
             COALESCE(DATE_3, DATE_2, DATE_1)
            )
FROM MYTABLE

Greatest not null column

COALESCE(GREATEST(date_one, date_two), date_one, date_two)

Oracle SQL - max() with NULL values

max(end_dt) keep (dense_rank first order by end_dt desc nulls first)

upd:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE t
    (val int, s date, e date)
;

INSERT ALL 
    INTO t (val, s, e)
         VALUES (1, sysdate-3, sysdate-2)
    INTO t (val, s, e)
         VALUES (1, sysdate-2, sysdate-1)
    INTO t (val, s, e)
         VALUES (1, sysdate-1, null)
    INTO t (val, s, e)
         VALUES (2, sysdate-1, sysdate-.5)
    INTO t (val, s, e)
         VALUES (2, sysdate-.5, sysdate-.25)
SELECT * FROM dual
;

Query 1:

select val, min(s), max(e) keep (dense_rank first order by e desc nulls first)
from t group by val

Results:

| VAL |                          MIN(S) | MAX(E)KEEP(DENSE_RANKFIRSTORDERBYEDESCNULLSFIRST) |
---------------------------------------------------------------------------------------------
|   1 | November, 13 2012 14:15:46+0000 |                                            (null) |
|   2 | November, 15 2012 14:15:46+0000 |                   November, 16 2012 08:15:46+0000 |

Find the greatest date value between columns with NULL values in oracle sql developer

You need to craft the ordering very carefully to correctly deal with nulls.

You can do:

select *
from (
  select t.*,
    row_number() over(
      partition by id
      order by case when a > b then case when c > a then c else a end
                    when a < b then case when c > b then c else b end
                    else c end DESC
    ) as rn
  from t
) x
where rn = 1

SQL query to find greatest in columns and rows

How about

select max(greatest(date1, date2, date3, date4)) from my_table;

---

Related Topics