title: MYSQL MAX/COUNT/IN/DATE
body: <p>Hope you can advise. Im pulling my hair out on a query and hope you can advise.</p>
  
  <p>I have a table where I want to select MAX from an inner count between dates with an IN.</p>
  
  <p><a href="http://www.screencast.com/users/openeye/folders/Jing/media/0b162b34-4f06-463d-a270-533a85858d28" rel="nofollow noreferrer">View Table</a></p>
  
  <p>Here is what I have tried but with no luck at all.</p>
  
  <pre><code>SELECT MAX(no_hits) from (SELECT count(hits) AS 'no_hits' ) FROM stats WHERE 'date' &gt;= DATE_SUB(CURDATE(), INTERVAL 1 MONTH AND zones_code IN('011242077793513596890', '011242077783866125432'))
  </code></pre>
  
  <p>So basically I only want one no_hits returned for the best performing zone.</p>
  
  <p>Hope you can advise on where im going wrong.</p>
  
  <p>And thank you if you cam</p>