var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Selecting a random record from top 100 records
Hope someone can help me with the following issue:
I would like to randomly pick up a row from a table. However the row that is randomly selected should be from the most recently (say, 50 or 100) posted entry in the table.
I was able to randomly pick a record (through rand()) or sort the top entries (through limit). However I am not able to combine both the above requirements.
Thanks in advance for your suggestions!
SELECT * FROM (SELECT * FROM tab ORDER BY rank LIMIT 100) x ORDER BY RAND() LIMIT 1
The subselect returns the 100 "top" rows. The outer select returns one of the rows.
I'd modify the above, there is no sense selecting all the data in the inner query. just select your primary key.
(select primarykeycolumn from your table order by rank limit 100) x
order by rand()
Selecting a random record from top 100 records
Thanks snorp & guelphdad for your suggestions.
It is now working beautifully!
huh? come again please?
Originally Posted by guelphdad
if the subquery selects only the pk, how can you possibly select any other columns in the outer query?
ooops, I was in a hurry and was thinking of two different things at once. Yes the above from snorp makes sense. I was thinking of it like this I guess (which doesn't seem any faster of course):
and of course didn't end up posting that.
where id in (select id from yourtable order by rank limit 100)
order by rand()