something similar to MAX in a mysql sql where clause

Question

I'm trying to write a sql function to do something like:

SELECT
    person.id, 
    person.phonenumber
FROM
    person INNER JOIN activity ON person.id = activity.personid
WHERE
    MAX(activity.activitydate) < DATE_SUB(CURDATE(), INTERAVAL 180 DAY);

Each time a person is contacted, we create an activity record for them with notes and the like. So I'm looking for all of the people, who haven't been contacted in the last 180 days. Obviously, this doesn't work, as max can't be used in the where clause.

I saw this, but mysql doesn't have the with statement.

Additionally, I tried

SELECT 
    person.id, 
    person.phonenumber, 
    MAX(activity.activitydate) as ndate
FROM 
    person INNER JOIN activity ON person.id = activity.personid
WHERE 
    ndate < DATE_SUB(CURDATE(), INTERVAL 180 DAY)
GROUP BY person.id;

but ndate wasn't known.

Any idea how I'd do this?

Answer 1

You need to use the HAVING clause:

  SELECT p.id, 
         p.phonenumber
    FROM PERSON p 
    JOIN ACTIVITY a ON a.personid = p.id
GROUP BY p.id, p.phonenumber
  HAVING MAX(a.activitydate) < DATE_SUB(CURDATE(), INTERAVAL 180 DAY)

...which means defining a GROUP BY clause.

Answer 2

You may use a having clause for this:

SELECT 
person.id, 
person.phonenumber, 
MAX(activity.activitydate) as ndate
FROM 
person INNER JOIN activity ON person.id = activity.personid
GROUP BY person.id
HAVING MAX(activity.activitydate) < DATE_SUB(CURDATE(), INTERVAL 180 DAY)

Answer 3

Depending on the size of your dataset/indexing you might also look at using "TOP 1" with an ORDER BY vs "MAX". TOP 1 is occasionally faster on dates.