Greatest not null column

Question

I need to update a row with a formula based on the largest value of two DATETIME columns. I would normally do this:

GREATEST(date_one, date_two)

However, both columns are allowed to be NULL. I need the greatest date even when the other is NULL (of course, I expect NULL when both are NULL) and GREATEST() returns NULL when one of the columns is NULL.

This seems to work:

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

But I wonder... am I missing a more straightforward method?

score 15 · Accepted Answer · answered Apr 21 '10 at 15:15

15

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

answered Apr 21 '10 at 15:15

Vadim K.

2,370
18
26

Nice! Thank you very much. I just hope I'll never need to generalize this for **n** columns... ;-P – Álvaro González Apr 22 '10 at 14:06
1

Ok, but what it there are more than 2 columns involved? What if there are 10 columns? – Nick Perkins Jul 08 '11 at 18:42
For n-columns: `COALESCE(GREATEST(date_one, date_two.. date_n), date_one, date_two, .. date_n)` – Sanjay Verma May 05 '21 at 20:14

score 1 · Answer 2 · answered Oct 14 '14 at 13:23

My solution for multiple columns is:

SELECT NULLIF(
  GREATEST(
    NVL(NULL,     to_date('01011980','ddmmyyyy')), --COLUMN 1
    NVL(sysdate,  to_date('01011980','ddmmyyyy')), --COLUMN 2
    NVL(NULL,     to_date('01011980','ddmmyyyy')), --COLUMN 3
    NVL(sysdate-1,to_date('01011980','ddmmyyyy'))  --COLUMN 4
  ),to_date('01011980','ddmmyyyy')
) as greatest_date
FROM DUAL;

Greatest not null column

2 Answers2

Linked