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)
The SQL Coalesce and IsNull functions are used to handle NULL values. During the expression evaluation process the NULL values are replaced with the user-defined value. The SQL Coalesce function evaluates the arguments in order and always returns first non-null value from the defined argument list.
The COALESCE function evaluates its arguments from left to right. It stops evaluating until it finds the first non-NULL argument. It means that all the remaining arguments are not evaluated at all. The COALESCE function returns NULL if all arguments are NULL .
Comparing COALESCE and ISNULL Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
The MySQL COALESCE() function is used for returning the first non-null value in a list of expressions. If all the values in the list evaluate to NULL, then the COALESCE() function returns NULL.
select greatest(c1, c2, c3, c4, c5)
from table;
Life can be so easy :-)
(edit: works on Oracle)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With