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?