Unanswered: select rows where id in (select...limit x) limit z Not Allowed?
I have a table with videos (id, title, views)
I want to display the most viewed videos.
I don't want to show always the same top ten por example.
The solution is to select the top 30 and then get 5 random from them.
MySQL version is 5.0.45 (lastest)
The query is:
SELECT * FROM video WHERE id IN (
FROM video v
ORDER BY visitas
LIMIT 30) x
ORDER BY rand()
(alias X and V are mandatory for MySQL)
The problem is I get this error: #1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
Is there any solution?
The only way is by using 2 queries and PHP. Isn't it?
The first one to get top 30 (just id's) and then top 10 where id IN (id´s from first query).