Clever Geek Handbook

How do I get the maximum date from two columns, or null if any column is null?

I have two date columns in a MySQL table representing tasks that two users are working on. Date columns record when user 1 agreed to the task and when user 2 agreed to the task, and perhaps NULL

if that user has not yet agreed to the task.

I want to generate an agreed date, which should be NULL if both users did not agree to the task, or at most two dates if they both agreed.

So, I want to get the request:

user_1_agreed  user_2_agreed  query_result
NULL           NULL           NULL
2014-01-02     NULL           NULL
NULL           2014-03-04     NULL
2014-01-02     2014-03-04     2014-03-04

I know MAX

, COALESCE

and IF

, but I'm having trouble trying to figure out how to combine them to give the above.

+3


source to share


2 answers


The function is greatest

great for your account:



SELECT GREATEST(user_1_agreed, user_2_agreed)
FROM   my_table
+4


source


try this, it should give you exactly what you want :)



select 
    case 
        when user_1_agreed is null then 'null'
        when user_2_agreed is null then 'null'
        when user_1_agreed  > user_2_agreed  then user_1_agreed  
        else user_2_agreed  
    end as finalvalues
from
    my_table
+1


source






More articles:


All Articles