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

sql null coalesce

10,623

Solution 1

Without overthinking it:

SELECT
  CASE WHEN c1 is null or c2 is null or c3 is null or c4 is null or c5 is null
       THEN null
       ELSE c1
  END
FROM mytable

My edit is as follows:

CASE 
 WHEN (c1 >= c2 AND c1 >= c3) THEN c1
 WHEN (c2 >= c1 AND c2 >= c3) THEN c2
 WHEN (c3 >= c1 AND c3 >= c2) THEN c3
END

Solution 2

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

Life can be so easy :-)

(edit: works on Oracle)

Solution 3

Try this:

SELECT
    CASE WHEN t1.SomeDate IS NULL THEN NULL ELSE MAX(t1.SomeDate) END AS TheVal
FROM
(
SELECT C1 AS SomeDate FROM Table_1
UNION ALL
SELECT C2 AS SomeDate  FROM Table_1
UNION ALL
SELECT C3 AS SomeDate FROM Table_1
UNION ALL
SELECT C4 AS SomeDate FROM Table_1
UNION ALL
SELECT C5 AS SomeDate FROM Table_1
) t1
GROUP BY
t1.SomeDate

10,623

Author by

Rick Rodriguez

Updated on June 18, 2022

Comments

Rick Rodriguez about 2 years
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)
Axn over 13 years

What is your output when you run the query against OP's data? Do you get two records or three?
Rick Rodriguez over 13 years

This is the nuts and bolts of what I needed. Thank you all very much, sincerely. As a guy learning SQL from the web, it's nice to have a resource to connect myself with people who know WAAAAY more than I. I wasn't aware if there was a built-in function or if I would have to use a case statement.
Axn over 13 years

I am getting 3 records, NULL, 1991 and 1992 when running your query. Are you sure you're using the right dataset?
Martin Schapendonk over 13 years

This would not return the max of the 5 if all five are not null.
Martin Schapendonk over 13 years

OK, first comment was inaccurate, I deleted it. @JonH As you smash all columns into rows with a union all, there is no guarantee that your solution will produce 1 result per original record. Change the original data to have 9 different dates and a null. Your query then returns 10 results instead of 2. That explains my downvote on your solution.
Axn over 13 years

OP specified that he was willing to accept returning any one of them.
Rick Rodriguez over 13 years

I appreciate all the help. I know this comment is way past due, but the real answer to this problem is in the same class as this solution provided.