title: apply a LIMIT to SQL INNER JOIN query
body: <p>I have the following tables:</p>
  
  <p><strong>CATEGORY</strong></p>
  
  <pre><code>    id(int)
      1000  
      1001
  </code></pre>
  
  <p><strong>BOOK</strong></p>
  
  <pre><code>    id(int)  category(int) rating(float)
      3000     1000          5.0
      3001     1000          4.8
      3002     1000          3.0
      3003     1000          4.9
      3004     1001          4.9
      3005     1001          3.0
  </code></pre>
  
  <p>What I want to do is take the 3 top rated books from each category. After looking around and following the answer given at <a href="https://stackoverflow.com/questions/494974/limiting-an-sql-join">LIMITing an SQL JOIN</a>, I tried this query.</p>
  
  <pre><code>    SELECT * FROM book, category WHERE book.category=category.id AND book.id IN (SELECT book.id FROM book ORDER BY rating LIMIT 3)
  </code></pre>
  
  <p>But it gives the following error</p>
  
  <pre><code>     #1235 - This version of MySQL doesn't yet support 'LIMIT &amp; IN/ALL/ANY/SOME subquery'
  </code></pre>
  
  <p>The solution said a minimum of <strong>MySQL 5.1</strong> was required, and I'm running <strong>libmysql - 5.1.73</strong>. What might be wrong?</p>