title: MySQL: Select MAX() from sub-query with COUNT()
body: <p>Before you mark this as duplicate please take a look at this <a href="http://sqlfiddle.com/#!2/336d6/1" rel="nofollow">SQLFiddle</a>.</p>
  
  <p>I have this schema:</p>
  
  <pre><code>CREATE TABLE book(book_id int,
                     book_name varchar(100),
                     author_id int,
                     editor_id varchar(100),
                     isbn varchar(100));
  INSERT INTO book
  VALUES
  (1 , 'Book1 Title' ,  12  ,  'Editor1'   , '8000-9000' ),
  (2 , 'Book2 Title' ,  98  ,  'Editor1'   , '8000-9001' ),
  (1 , 'Book1 Title' ,  12  ,  'Editor1'   , '8000-9002' ),
  (3 , 'Book3 Title' ,  3  ,  'Editor1'   , '8000-9003' );
  
  CREATE TABLE author(author_id int,
                      fn varchar(100),
                      ln varchar(100));
  INSERT INTO author
  VALUES
  (12, 'name1','lname1'),
  (98,'name2','lname2'),
  (3,'name3','lname3');
  </code></pre>
  
  <p>The sub-query:</p>
  
  <pre><code>  SELECT c.author_id,COUNT(*) book_count FROM book c
    GROUP BY c.author_id
  </code></pre>
  
  <p>has a result:</p>
  
  <pre><code>| AUTHOR_ID | BOOK_COUNT |
  --------------------------
  |         3 |          1 |
  |        12 |          2 |
  |        98 |          1 |
  </code></pre>
  
  <p>Now, the tricky part here is the result of this query:</p>
  
  <pre><code>SELECT MAX(book_count),a.* FROM
  author a,(
    SELECT c.author_id,COUNT(*) book_count FROM book c
    GROUP BY c.author_id
  ) b 
  where a.author_id = b.author_id
  </code></pre>
  
  <p>is this:</p>
  
  <pre><code>| MAX(BOOK_COUNT) | AUTHOR_ID |    FN |     LN |
  ------------------------------------------------
  |               2 |         3 | name3 | lname3 |
  </code></pre>
  
  <p>which should be like this:</p>
  
  <pre><code>| MAX(BOOK_COUNT) | AUTHOR_ID |    FN |     LN |
  ------------------------------------------------
  |               2 |        12 | name1 | lname1 |
  </code></pre>
  
  <p>What do you think is wrong in the query?</p>