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....

Matt Dodge · Accepted Answer · 2015-03-23T17:17:35.467

130

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.

edited Mar 23 '15 at 17:17

answered Mar 22 '12 at 23:11

Matt Dodge

10,833
7
38
58

btw this assumes that if the date is null it will not be included in the greatest calculation – Matt Dodge Mar 22 '12 at 23:12
6

To clarify what COALESCE is doing (because I didn't understand it straight away so thought it might help others to have an explanation). COALESCE returns the first Non Null value, so by passing a date and 0 then if the date is null, the output of COALESCE(date, 0) would be 0, and you'll be taking the greatest between a date and 0, resulting in your date. I didn't understand why it worked at first but now I do. Futher reading https://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php – Violet Flare Aug 06 '18 at 14:07
5

@MattDodge - you made mention of a comment by "Mike Chamberlain" which no longer appears to exist. Can you explain why `COALESCE()` would be preferred over `IFNULL()`? I'd think the latter would be more performant. – But those new buttons though.. Jul 25 '19 at 16:31
2

@billynoah well it's been 7+ years so my memory is a bit foggy haha, but I believe there were some docs that explicitly recommended to use COALESCE because it is part of the generic SQL spec or something like that. I'm not clear on performance impact though. – Matt Dodge Jul 25 '19 at 18:41
1

@MattDodge - thanks, I did a little digging and came to the same conclusion – But those new buttons though.. Jul 26 '19 at 00:28

score 31 · Answer 2 · answered Mar 22 '12 at 23:12

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

score 6 · Answer 3 · answered Mar 22 '12 at 23:10

6

COALESCE your date columns before you use them in GREATEST.

The way you handle them will depend on how you want to deal with NULL.. either high or low?

answered Mar 22 '12 at 23:10

hkf

4,440
1
30
44

score 5 · Answer 4 · answered May 06 '17 at 01:24

buuut, if all dates happen to be null? you still want to have null as an output, right? then you need this

select nullif(greatest(coalesce(<DATEA>, from_unixtime(0)), coalesce(<DATEB>, from_unixtime(0))), from_unixtime(0));

Now, if both are null you get null, if one of them is not null of both of them are not null, you get the greatest.

This is crazy, especially if you will use it multiple times, for this then you might want to create it as a function, like this:

delimiter //
drop function if exists cp_greatest_date//
create function cp_greatest_date ( dateA timestamp, dateB timestamp ) returns timestamp
  deterministic reads sql data
  begin

    # if both are null you get null, if one of them is not null of both of them are not null, you get the greatest
    set @output = nullif(greatest(coalesce(dateA, from_unixtime(0)), coalesce(dateB, from_unixtime(0))), from_unixtime(0));
    # santiago arizti

    return @output;
  end //
delimiter ;

Then you can use it like this

select cp_greatest_date(current_timestamp, null);
-- output is 2017-05-05 20:22:45

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

4 Answers4

Linked