Handling Null in Greatest function in Oracle

Question

I want to compare two dates from two columns and get the greatest and then compare against a date value. The two column can hold NULL values too.

For example I want the below OUTPUT.

Col A         Col  B          OUTPUT
---------------------------------------
 NULL          NULL            NULL
 09/21/2013    01/02/2012      09/21/2013
 NULL          01/03/2013      01/03/2013 
 01/03/2013    NULL            01/03/2013

How do I use the greatest function or if there is anything else? I am again using the output to compare against another date.

score 33 · Answer 1 · answered Aug 29 '16 at 16:25

Your question specifically involves two columns, but I've run into situations where I needed GREATEST/LEAST of more than two columns. In those scenarios you can use COALESCE and expand the solution to as many columns you want.

Here is an example with three columns a, b, and c:

GREATEST(
    COALESCE(a, b, c),
    COALESCE(b, c, a),
    COALESCE(c, a, b)
)

Note that the column ordering of the COALESCE changes so that each input column is the first element COALESCE at least once. The only time this will return NULL is when all input columns are NULL.

In the "general solution" the number of COALESCE statements will be equal to the number of input columns:

GREATEST(
    COALESCE(col1, col2, col3, col4, ....),
    COALESCE(col2, col3, col4, ...., col1),
    COALESCE(col3, col4, ...., col1, col2),
    COALESCE(col4, ...., col1, col2, col3),
    COALESCE(...., col1, col2, col3, col4),
    ...
)

score 24 · Answer 2 · answered Feb 08 '16 at 03:00

24

You might try the following:

SELECT cola, colb, COALESCE( GREATEST( cola, colb ), cola, colb ) AS output
  FROM yourtable;

The reason for COALESCE() is that GREATEST() returns NULL if either of the parameters is NULL.

answered Feb 08 '16 at 03:00

David Faber

12,277
2
29
40

score 7 · Answer 3 · answered Oct 04 '13 at 16:41

Another version using a case expression to handle the null values:

select cola, colb, 
  case when cola is null and colb is null then null
    when cola is null then colb
    when colb is null then cola
    else greatest(cola, colb)
  end as output
from <table>;

COLA       COLB       OUTPUT   
---------- ---------- ----------

09/21/2013 01/02/2012 09/21/2013 
           01/03/2013 01/03/2013 
01/03/2013            01/03/2013

score 7 · Answer 4 · answered Mar 14 '18 at 10:47

You could remove the possibility of any of the columns returning NULL by using the NVL function. Substitute any NULL values with a date that is earlier than any date that is likely to occur in your tables.

SELECT GREATEST(NVL(A,TO_DATE('01/01/1800','MM/DD/YYYY')), 
                NVL(B,TO_DATE('01/01/1800','MM/DD/YYYY'))) AS OUTPUT
FROM ...

The GREATEST function will then return the most recent date (maximum date) from the list of supplied dates without inadvertently returning NULL if one or more of the columns contains NULL.

score 4 · Accepted Answer · edited Jan 22 '21 at 14:46

4

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 ...

edited Jan 22 '21 at 14:46

xnagyg

4,784
2
32
24

answered Oct 04 '13 at 16:37

Maxime Pacary

22,336
11
85
113

score 3 · Answer 6 · answered Oct 04 '13 at 18:17

If you have many columns to compare (more than 2 or 3), then handling all the various CASE combinations might get unwieldy. You could try (11g):

with x as (
  select 1 as id, sysdate - 30 as col1, sysdate-50 as col2, sysdate-20 as col3,null as col4, sysdate-1 as col5 from dual
  union
  select 2 as id, sysdate - 10 as col1, sysdate-20 as col2, null as col3,null as col4, sysdate-35 as col5 from dual
  union
  select 3 as id, null as col1, null as col2, null as col3, null as col4, null as col5 from dual
)
select id, max(dates)
from x
UNPIVOT INCLUDE NULLS
(dates FOR colname IN (col1,col2,col3,col4,col5))
group by id

score 0 · Answer 7 · answered Oct 04 '13 at 16:39

0

Something like

SELECT CASE WHEN ColA is NULL and ColB is NULL then NULL
WHEN coalesce(ColA, '01/01/1753')>coalesce(ColB, '01/01/1753') then ColA
ELSE ColB END as Output

answered Oct 04 '13 at 16:39

Hedinn

864
4
7

3

That works if you use `to_date()` around the magic dates, ideally with a specific date format model; or preferably (personally) using ANSI date notation as `date '1753-01-01'`. – Alex Poole Oct 04 '13 at 16:50

score 0 · Answer 8 · answered Oct 04 '13 at 16:50

0

I tried this..found after googling

WITH ABC AS ( SELECT NULL AS col1 , NULL AS col2 FROM dual UNION
 SELECT NULL , DATE'2013-08-12' FROM dual UNION
  SELECT  DATE'2013-08-12' , NULL FROM dual UNION
   SELECT  DATE'2013-08-12',  DATE'2013-09-12' FROM dual)

   SELECT col1, col2 , substr(greatest('x'||col1,'x'||col2),2)
      FROM ABC;

answered Oct 04 '13 at 16:50

Rajiv A

941
5
14
30

1

This is doing an implicit conversion of your date columns to strings, using whatever your current `NLS_DATE_FORMAT` is, and then comparing the strings, not the dates. With your `MM/DD/YYYY` setting, it won't work if the dates are in different years; in your last row change the second date to `2012-09-12` and it will still report that as 'greater than' `2013-08-12` - because it's actually comparing `'09/12/2012'` and `'08/12/2013'`, and that's true for a string comparison... – Alex Poole Oct 04 '13 at 16:58

score 0 · Answer 9 · edited Nov 12 '19 at 16:06

use NVL to solve this however complicity will be increased based of number of compared columns :

select A.*, *greatest("COL A", "COL B") "DIRECT COMPARE"*, **greatest(nvl("COL A", "COL B"), nvl("COL B", "COL A")) "NVL COMPARE"**
from (
SELECT NULL "COL A", SYSDATE "COL B", SYSDATE "NEEDED RESULT" FROM DUAL UNION
SELECT SYSDATE - 180 , NULL  , SYSDATE - 180 FROM DUAL UNION
SELECT SYSDATE - 180 , SYSDATE , SYSDATE FROM DUAL ) A;

Handling Null in Greatest function in Oracle

9 Answers9

Linked