Solutions to the ORDER BY RAND() problem.

Just getting them in there, these are not original ideas, I got them from someone else, just wanted to post them on my blog for convenience sakes:


* Run a query to determine the MAX ID number in the table.
$MAX = SELECT COUNT(*) FROM tbl

/* Use PHP to generate a random number between 1, and the max
$ID = RAND(1, $MAX)

/* Run a last query to select the ID that is >= the random one.
SELECT * FROM tbl WHERE id >= $ID LIMIT 1


Another possible solution would be a heavy modification to that query. This solution is likely the best.

New Query:

SELECT *
FROM `tbl` AS `l1`
JOIN
(SELECT (RAND() * (SELECT MAX(`id`) FROM `tbl`)) AS `id`) AS `l2`
WHERE
`l1`.`id` >= `l2`.`id`
ORDER BY
`l1`.`id` ASC
LIMIT 1;

159 thoughts on “Solutions to the ORDER BY RAND() problem.

  1. Only works when you want one result. Back to the drawing board if you want more than one. Might be better to run in a loop, pseudo code:

    $max = SELECT MAX(`id`) FROM `tbl`)
    $results = array()
    $numToGet = 100
    while (count($results) < $numToGet) SELECT * FROM `tbl` AS `l1` JOIN (SELECT (RAND() * $max) AS `id`) AS `l2` WHERE `l1`.`id` >= `l2`.`id`
    AND l1.id NOT IN ($results)
    ORDER BY
    `l1`.`id` ASC
    LIMIT 1
    INTO $results[]

    end while

Comments are closed.