A correlated subquery can also be used in the HAVING clause of an outer query. This construction can be used to find the types of books for which the maximum advance is more than twice the average within a given group.
In this case, the subquery is evaluated once for each group defined in the outer query (once for each type of book).
USE pubs SELECT t1.type FROM titles t1 GROUP BY t1.type HAVING MAX(t1.advance) >=ALL (SELECT 2 * AVG(t2.advance) FROM titles t2 WHERE t1.type = t2.type)
Here is the result set:
type -------- mod_cook (1 row(s) affected)