MySQL: get MAX or GREATEST of several columns, but with NULL fields

Question

I'm trying to select the max date in three different fields in each record (MySQL) So, in each row, I have date1, date2 and date3: date1 is always filled, date2 and date3 can be NULL or empty The GREATEST statement is simple and concise but has no effects on NULL fields, so this doesn't work well:

SELECT id, GREATEST(date1, date2, date3) as datemax FROM mytable 

I tried also more complex solutions like this:

SELECT     CASE         WHEN date1 >= date2 AND date1 >= date3 THEN date1         WHEN date2 >= date1 AND date2 >= date3 THEN date2         WHEN date3 >= date1 AND date3 >= date2 THEN date3         ELSE                                        date1     END AS MostRecentDate 

Same problem here: NULL values are a GREAT problem in returning the right records

Please, have you got a solution? Thanks in advance....

Answer 1

Use COALESCE

SELECT id,     GREATEST(date1,       COALESCE(date2, 0),      COALESCE(date3, 0)) as datemax  FROM mytable 

Update: This answer previously used IFNULL which does work, but as Mike Chamberlain pointed out in the comments, COALESCE is actually the preferred method.

Answer 2

If date1 can never be NULL, then the result should never be NULL, right? Then you could use this, if you want NULL dates be not counted in the calculations (or change the 1000-01-01 to 9999-12-31, if you want Nulls to count as the "end of time"):

GREATEST( date1         , COALESCE(date2, '1000-01-01')         , COALESCE(date3, '1000-01-01')         ) AS datemax