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.
2
Answers
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.
I would prefer
GREATEST
withCOALESCE
here:MAX
is unhandy in this situation due to its limitation to one argument, see also this article: difference max <-> greatestCOALESCE
is required in this case becauseGREATEST
is not able to deal withNULL
values in MYSQL.Try out: db<>fiddle