Apply LIMIT query to SQL INNER JOIN

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 to take 3 books with the highest rating from each category. After inspecting and completing the answer given in SQL JOIN RESTRICTION , 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 that a minimum of MySQL 5.1 is required , and I run libmysql - 5.1.73 . What could be wrong?

Answer 1

MySQL does not support LIMITin subqueries for specific subquery operators:

'LIMIT & IN/ALL/ANY/SOME subquery'

for instance

mysql> SELECT * FROM t1
    ->   WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);
ERROR 1235 (42000): This version of MySQL doesn't yet support
 'LIMIT & IN/ALL/ANY/SOME subquery'

Read Subquery Constraint

Answer 2

, , LIMIT 3 LIMIT 0,3: :

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

^^

Answer 3

MySQL complains about IN, not about LIMIT. Change INto INNER JOINand 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;