Return random rows from the best ones (i.e: 100 rows -> best 10 -> get 5 random)

Question

Hello all again!

Situation: A table with lots of photos, classified and with votes. I'm currently doing this:

SELECT n.nid, n.title, ....... order by v.value desc limit 0,5

What I want is to get a random resultset after sorting my rows by its votes. Now, a table with hundreds of records is giving me the best 5 rows. Always the same 5. This is quite repetitive, but of course giving 5 random rows wouldn't be the best thing, as not all the rows have a good quailty photo. Some might be not too good.

I don't want to just do this:

SELECT n.nid, ...... order by RAND() limit 0,5

What I'm looking for is something like this:

SELECT n.nid, .....   order by RAND( v.value desc limit 0.10) limit 0,5

but of course that is not SQL :) The tables involved have up to 50.000 rows.

Thanks!

Answer 1

Wouldn't a nested query do the trick? First select your 10 best photos, then select 5 randomly. Or a temporary table, but it's almost the same a nested query.

Edit: thanks for the link Lukáš