apply a LIMIT to SQL INNER JOIN query [duplicate]

Question

I have the following tables:

CATEGORY

    id(int)
    1000  
    1001

BOOK

    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

What I want to do is take the 3 top rated books from each category. After looking around and following the answer given at LIMITing an SQL JOIN, I tried this query.

    SELECT * FROM book, category WHERE book.category=category.id AND book.id IN (SELECT book.id FROM book ORDER BY rating LIMIT 3)

But it gives the following error

     #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

The solution said a minimum of MySQL 5.1 was required, and I'm running libmysql - 5.1.73. What might be wrong?

Answer 1

MySQL is complaining about the IN, not about the LIMIT. Change the IN to INNER JOIN and it should work.

Something like this query:

SELECT * FROM book b, category INNER JOIN (SELECT id FROM book ORDER BY rating LIMIT 3) v
ON b.id=v.id
WHERE b.category=category.id;