2
select .. from (
    Select ... from ... order by weight desc limit N
    ) order by rand() limit 1

The above needs to create a temporary table each time,which is not efficient,so it doesn't qualify.

How to do it properly?

user198729
  • 61,774
  • 108
  • 250
  • 348
SQL
  • 21
  • 1

2 Answers2

1

If I understand correctly, you want the Rth row from an ordered result set where R is a random number. If so, then it seems the LIMIT option with two parameters is the one you want. The first parameter could be the random number from 1 to N:

SELECT ... order by weight desc limit R,1

I don't have MySQL installed, so I can't test it. So I do not know if R can use RAND() directly or if it would have to be precomputed.

Mark Wilkins
  • 40,729
  • 5
  • 57
  • 110
  • Nice suggestion. Though LIMIT clause can not contain expressions in MySQL. `COUNT(*) >= N` should also be true. – newtover Apr 20 '10 at 20:56
  • @SQL: If you generate random value in client code and the count of rows in a table is less than N, the random value can fall outside the range and the resulting query would return no rows. – newtover Apr 21 '10 at 08:10
0

You should take a look at:

http://akinas.com/pages/en/blog/mysql_random_row/

There are several suggestions for implementing this while avoiding table scans, including:

SELECT * FROM `table` WHERE id >= (
        SELECT FLOOR( MAX(id) * RAND()) FROM `table` 
    ) ORDER BY id LIMIT 1;
awgy
  • 16,596
  • 4
  • 25
  • 18