SQL Return Null if One Column is Null (Opposite of COALESCE())

sql null coalesce

Rick Rodriguez · Nov 17, 2010 · Viewed 10.3k times · Source

In advance, I would like to say thanks for the help. This is a great community and I've found many programming answers here.

I have a table with multiple columns, 5 of which contain dates or null.

I would like to write an sql query that essentially coalesces the 5 columns into 1 column, with the condition that if 1 of the five columns contains a "NULL" value, the returned value is null. Essentially the opposite of the coalesce condition of returning the first non-null, I want to return the first null. If none are null, returning the greatest of the 5 dates would be optimal, however I can settle with returning any one of the 5 dates.

    C1         C2          C3        C4        C5
    --         --          --        --        --
 1/1/1991   1/1/1991    1/1/1991  1/1/1991  2/2/1992
   NULL     1/1/1991    1/1/1991  1/1/1991  1/1/1991

Query Returns:

    C1
    --
 2/2/1992
   NULL

Thank you very much.

(Server is MSSQL2008)

Answer

select greatest(c1, c2, c3, c4, c5)
from table;

Life can be so easy :-)

(edit: works on Oracle)

SQL Return Null if One Column is Null (Opposite of COALESCE())

Answer

Related questions