MySQL - Referencing aggregate column in where clause
aggregate, max, mysql, sql
Solution
You want the `having` clause.
General rule: `where` operates on date before aggregation, `having` operates on data after aggregation.
Problem
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.