MySQL: get MAX or GREATEST of several columns, but

问题:

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

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

回答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

回答3:

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?

回答4:

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