How to get max date from two columns, or null if either column is null?


Problem :

I have two date columns in a MySQL table representing tasks that are worked on by two users. The date columns record when user 1 agreed to the task and when user 2 agreed to the task, and can be NULL if that user hasn't agreed to the task yet.

I want to generate the date task agreed - which should be NULL unless both users have agreed to the task, or the maximum of the two dates if they've both agreed.

So I want a query that produces:

user_1_agreed  user_2_agreed  query_result
NULL           NULL           NULL
2014-01-02     NULL           NULL
NULL           2014-03-04     NULL
2014-01-02     2014-03-04     2014-03-04

I'm aware of MAX, COALESCE and IF, but I'm having trouble trying to work out how to combine them to give the above.



Solution :

The greatest function fits your bill perfectly:

SELECT GREATEST(user_1_agreed, user_2_agreed)
FROM   my_table

Mysql Tutorials

Mysql Howto..

How to solve make mysql-connector-c++-1.1.7 error

How to extend User properly with mysql in Loopback

How to retrieve a default row when a mysqli_stmt_fetch fails?

How to work on Mysql inside php functions? [closed]

php and mysql how to join a table [duplicate]

How to mysql_real_escape_string a zero

How to escape commas inside CSV values when importing table to MySQL?

How to pass variables in the MySQL connection string in php

How to lay out my database?

MySQL: how to transform this using INNER JOIN

How to get notified by mail when rows are inserted into table?

How to update my mysql database using if condition

How to create 1000 rows with the same values in columns?

How to make a self to self relationship in Mysql?

How to display values from two separate tables from mySQL database?

How to send account recharge notifications to users, PHP+MySQL

How do protect yourself against SQL injection when using prepared statements/store procedures in PHP?

How to query a highscore list by most recent score in Sql?

How to show mysql tables in one line? [duplicate]

How can I get mysql to output a DateTime to Julian day number?

How to write mysql query with WHERE 1 in Codeigniter Active record form

Using spring to connect to mysql, how to mimick connection pool?

upgrading from php 5.3 to php 5.5 showing connection error [closed]

How to dump mysql table structure without data with a SQL query?

How to get counts in Mysql Left Join?

MySQL how to ignore indexig while inserting rows

How to set up a MySQL database effectively for large text and html fields

How to retrieve values stored in JSON array in MySQL query itself?

How to deal with Sedna via PHP

How do I select titles from a mysql DB that begin with a specific letter?