6

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?

Community
  • 1
  • 1
Suhair Zain
  • 403
  • 4
  • 15
  • I just mentioned it because I am running this query from a PHP script. I've removed it, sorry. :) – Suhair Zain May 20 '15 at 13:28
  • 1
    Check this topic: http://stackoverflow.com/questions/3333665/rank-function-in-mysql. You should rank the rows partitioning them by category and select the ones with rank <= 3 - unfortunately, MySQL does not have any ready syntax for that, unlike PostgreSQL, Oracle or MS SQL, so some workarounds are necessary. – Vadim Landa May 20 '15 at 13:30
  • I need to run the same query on 2 databases, and one of them is in Android, which uses SQLite. Can I do it there? – Suhair Zain May 20 '15 at 13:43

3 Answers3

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;
Fernando Garcia
  • 1,946
  • 2
  • 12
  • 14
  • I'm sorry. I'm a beginner in SQL, and I tried replacing the IN with INNER JOIN even though it seemed like the syntax was wrong and the error was this. `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INNER JOIN (SELECT book.id FROM book ORDER BY rating LIMIT 3) LIMIT 0, 30' at line 1` – Suhair Zain May 20 '15 at 13:34
  • This error is given `#1054 - Unknown column 'book.category' in 'where clause' ` – Suhair Zain May 20 '15 at 13:46
  • 1
    Edited the answer, but to help you writing a proper query, we need to know your table structure. – Fernando Garcia May 20 '15 at 13:50
  • Thanks. The **BOOK** table contains more fields, but I believe they are not of any use because in this query, I only consider the rating and category. – Suhair Zain May 20 '15 at 14:02
  • Also, the query you suggested gives the following error `#1054 - Unknown column 'b.book.id' in 'on clause' ` – Suhair Zain May 20 '15 at 14:03
  • Oops, typo, sorry. I think it should work now, I have not time to copy your schema and test it myself right now. – Fernando Garcia May 20 '15 at 14:14
0

MySQL does not support LIMIT in subqueries for certain subquery operators:

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

For example

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 limitation

Saty
  • 22,443
  • 7
  • 33
  • 51
0

There is a trick to do it just change LIMIT 3 to LIMIT 0,3 : like this : 

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

Source ^^

Maraboc
  • 10,550
  • 3
  • 37
  • 48