Something similar to MAX in mysql sql where clause

Question

I am 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);

Every time a person contacts someone, we create an activity record for them with notes, etc. Therefore, I am looking for all the people who have not been contacted for the past 180 days. Obviously, this does not work, since max cannot be used in the where clause.

I saw this , but mysql does not have a with statement.

I have also 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 was not known.

Any idea how I would do this?

Answer 1

You need to use a sentence HAVING:

  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(), INTERVAL 180 DAY)

... which means the definition of a sentence GROUP BY.

Answer 2

having :

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 / index, you can also use "TOP 1" with ORDER BY versus "MAX". TOP-1 is sometimes faster by date.