CODEWITHSUNDEEP
sqlmysql
Steven Evers
Steven Evers

Reputation: 17206

something similar to MAX in a mysql sql where clause

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?

Upvotes: 4

Views: 4943

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332681

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

...which means defining a GROUP BY clause.

Upvotes: 9

jasonk
jasonk

Reputation: 1570

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.

Upvotes: 0

Vincent Ramdhanie
Vincent Ramdhanie

Reputation: 103145

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)

Upvotes: 0

Related Questions