skip to Main Content

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


  1. 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";

    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search