[djuritz]

Hi.

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 (
SELECT id
FROM video v
ORDER BY visitas
LIMIT 30) x
ORDER BY rand()
LIMIT 10

(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).

Thanks a lot!

[r937]

my advice: return the top 30, then use php to pick 10 at random

keeps the query simple