MySQL: get MAX or GREATEST of several columns, but with NULL fields
Solution 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.
Solution 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
Solution 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?
Solution 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
Ivan
Updated on May 06, 2020Comments
-
Ivan about 4 years
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 about 12 yearsbtw this assumes that if the date is null it will not be included in the greatest calculation
-
Violet Flare almost 6 yearsTo 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 w3resource.com/mysql/comparision-functions-and-operators/…
-
Eaten by a Grue almost 5 years@MattDodge - you made mention of a comment by "Mike Chamberlain" which no longer appears to exist. Can you explain why
COALESCE()
would be preferred overIFNULL()
? I'd think the latter would be more performant. -
Matt Dodge almost 5 years@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.
-
Eaten by a Grue almost 5 years@MattDodge - thanks, I did a little digging and came to the same conclusion