MySql Query for Max(Date) not Today

Question

I have the following table:

Name - Date
Adam - 7-13-19
Stan - 7-13-19
Adam- 7-11-19
Bob - 7-10-19
Stan- 7-10-19
Eric- 7-10-19
Lucy- 7-09-19
Eric- 7-09-19
Bob - 7-09-19
Lucy - 7-08-19

I would like to pull each unique name (which I'm using GROUP BY to do) and the latest corresponding date for each name (which I'm using Max(date) to do). However, I don't want the name to appear if the max(date) is equal to today's date (7-13-19). So the end result I'm looking for is:

Bob
Eric
Lucy

The list would not include Adam or Stan...since they both have max dates of 7-13-19. I've tried a whole bunch of queries, but can't get it to work. My initial thought was something like this:

SELECT *, max(date)
FROM table
WHERE max(date) <> date(now())
GROUP BY name

Any help would be greatly appreciated.

Answer 1

If you want to use aggregate results as a filter, you need to use the HAVING clause.

Something like this should work for you.

SELECT name
    , MAX(date) AS max_date
FROM table 
GROUP BY name
HAVING MAX(date(date)) <> DATE(NOW())