0

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.

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()) 
5
  • It's grouping them together; however, it still pulling all of the max(date)s and not excluding the ones that match the date(now()). any thoughts? Jul 14 '19 at 2:59
  • To clarify the source table the data is dates and not date times? (Even a midnight value is not equal to date(now())) Jul 14 '19 at 3:01
  • it's a timestamp that includes the dates and time. does that matter? Jul 14 '19 at 3:13
  • See my updated answer. Jul 14 '19 at 3:14
  • I think that did the trick! :-) Thanks! Jul 14 '19 at 3:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.