MySQL - Referencing aggregate column in where clause

Question

This seems so simple but I can't seem to figure it out without doing subqueries (which seem to slow down the queries significantly - takes almost 10 seconds instead of <1).

Let's say I have a table of sent documents, and I want to select the ones that have been updated since they've last been sent, and the ones that have never been sent.

SELECT d.document_id, max(sd.document_sent_date) as last_sent_date
FROM documents d
LEFT JOIN sent_documents sd ON d.document_id=sd.document_id
WHERE last_sent_date is NULL OR last_sent_date<d.last_updated
GROUP BY d.document_id

Is something like this possible? Basically, I want to use the result of max() in my where clause.

Answer 1

You want the having clause.

General rule: where operates on date before aggregation, having operates on data after aggregation.