Question: 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.

Answer: 
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";
> 
> 
>
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](https://database.guide/max-vs-greatest-in-mysql-whats-the-difference/)

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

Try out: [db<>fiddle](https://dbfiddle.uk/VC2ioxd5)