How to select the bigger date between 2 columns?-CodePudding

I have a table 'processes' with the following columns :

id
date_creation
date_lastrun

For example I have the following entries:

id;date_creation;date_lastrun
1;2022-01-01 00:00:00;2022-02-01 00:00:00
2;2022-03-01 00:00:00;NULL

I want to select the element with the bigger date in MySQL I can do

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes

It's OK it works but now I want to get the element with the bigger date compared to a specific date time. I tried :

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes 
WHERE DATE(lastdate) > "2022-03-01"

but it returns *#1054 - Unknown column 'lastdate' in 'where clause'

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate 
FROM processes 
WHERE DATE(MAX(IFNULL(date_lastrun, date_creation))) > "2022-03-01"

but it returns #1111 - Invalid use of group function

Do you have any idea how to accomplish that?

I hope to return the element with the bigger date compared to a specific date.

CodePudding user response：

Do not use the MAX in the WHERE clause but limit the result to dates bigger than "2022-03-01" and then get the biggest one.

SELECT id, MAX(IFNULL(date_lastrun, date_creation)) as lastdate FROM processes WHERE DATE(IFNULL(date_lastrun, date_creation)) > "2022-03-01";

CodePudding user response：

I would prefer GREATEST with COALESCE here:

SELECT id, GREATEST(COALESCE(date_creation,0), COALESCE(date_lastrun,0)) AS lastdate
FROM processes
WHERE GREATEST(COALESCE(date_creation,0), COALESCE(date_lastrun,0)) > "2022-03-01";

MAX is unhandy in this situation due to its limitation to one argument, see also this article: difference max <-> greatest

COALESCE is required in this case because GREATEST is not able to deal with NULL values in MYSQL.

Try out: db<>fiddle