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

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


mysql null max

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.

mysql null max

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

mysql null max

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?

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last