Selecting a random record from top 100 records

sqlcc · #1 (**permalink**) 01-08-07, 00:49

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!
--

snorp · #2 (**permalink**) 01-08-07, 02:33

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.

guelphdad · #3 (**permalink**) 01-08-07, 18:49

I'd modify the above, there is no sense selecting all the data in the inner query. just select your primary key.

Code:

select
foo,
bar,
qux
from
(select primarykeycolumn from your table order by rank limit 100) x
order by rand()
limit 1

sqlcc · #4 (**permalink**) 01-08-07, 19:58

Thanks snorp & guelphdad for your suggestions.

It is now working beautifully!

--

r937 · #5 (**permalink**) 01-08-07, 20:17

Quote:

Originally Posted by guelphdad

I'd modify the above, there is no sense selecting all the data in the inner query. just select your primary key.

huh? come again please?

if the subquery selects only the pk, how can you possibly select any other columns in the outer query?

guelphdad · #6 (**permalink**) 01-08-07, 23:59

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):

Code:

select
id,
foo,
bar,
qux
from yourtable
where id in (select id from yourtable order by rank limit 100)
order by rand()
limit 1

and of course didn't end up posting that.

Internet Services

Web Development

Digital Marketing

Consumer Tech