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.

| improve this question | |
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())
| improve this answer | |
  • 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? – adamhaynes17 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())) – Jonathan Fite Jul 14 '19 at 3:01
  • it's a timestamp that includes the dates and time. does that matter? – adamhaynes17 Jul 14 '19 at 3:13
  • See my updated answer. – Jonathan Fite Jul 14 '19 at 3:14
  • I think that did the trick! :-) Thanks! – adamhaynes17 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.