title: Select statement with MAX() aggregation in the Where Clause
body: <p>I have a database table which stores membership renewals each year.  When insert the renewal record, an 'expiryDate' column is written with a date (31/8/[nextyear]).</p>
  
  <p>So, as an example, let's say a member with memberID = 99 renews in 2007, 2008 and 2009, he will have 3 records (one for each year), with an 'expiryDate' recorded in each.  If I do a </p>
  
  <pre><code>SELECT MAX(YEAR(expiryDate)) as maxExpiry 
    FROM renewals 
   WHERE memberID = 99
  </code></pre>
  
  <p>...I will get 2010 back.</p>
  
  <p>What I would like to do is return ALL records where the <code>MAX(YEAR(expiryDate))</code> is a given year.. for example, </p>
  
  <pre><code>SELECT * 
    FROM renewals 
   WHERE MAX(YEAR(expiryDate)) = '2010';
  </code></pre>
  
  <p>This query won't work as an aggregation can't be used in a where clause outside a subquery, but I can't quite work out how to structure the subquery... or even if this could be done a better way than using a subquery.</p>