title: MYSQL entries from one table that appear most often in another table
body: <p>I have 2 tables, authors and books</p>
  
  <p>authors contains the unique id authorId</p>
  
  <p>books also contains this as a foreign key</p>
  
  <p>I need to know the authors with the most number of books. If 2 or more authors are tied for the greatest number of books, I need to show both authors</p>
  
  <p>I have been able to achieve this by first getting the maximum count</p>
  
  <pre><code>SELECT @maxCount := (MAX(counter)) FROM (SELECT count(*) AS counter FROM books GROUP BY authorId) AS counts;
  </code></pre>
  
  <p>and then using it to get the Ids with that count as part of my author selection</p>
  
  <pre><code>SELECT * 
  FROM authors 
  WHERE authorId IN (
      SELECT authorId 
      FROM books 
      GROUP BY authorId 
      HAVING COUNT(*) = @maxCount
  );
  </code></pre>
  
  <p>I've been told that I am not allowed to use variables and that what I've done is horribly inefficient if the tables grow very large.</p>
  
  <p>Am I missing something obvious here? Is there a way to do this in a single statement without a variable (or temp table), and without having to select/group the entire books table twice?</p>