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

mysql null max
Ivan picture Ivan · Mar 23, 2012 · Viewed 36.7k times · Source

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

Matt Dodge picture Matt Dodge · Mar 23, 2012

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.

Related questions

How set 0 with MAX function when it is NULL?

I would like to understand how to set 0 value of the attribute when it is NULL with MAX function. For example: Name columns: number - date Values: 10 - 2012-04-04 11 - 2012-04-04 12 - 2012-04-04 13 - 2012-04-15 14 - 2012…

Read More
MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More
MySQL and PHP - insert NULL rather than empty string

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do …

Read More