Statistical Techniques - MySQL - page 527

15.7. Randomizing a Set of Rows
Problem
You want to randomize a set of rows or values.
Solution
Use ORDER BY RAND() .
Discussion
MySQL's RAND() function can be used to randomize the order in which a query returns
its rows. Somewhat paradoxically, this randomization is achieved by adding an ORDER
BY clause to the query. The technique is roughly equivalent to a spreadsheet randomi‐
zation method. Suppose that a spreadsheet contains this set of values:
Patrick
Penelope
Pertinax
Polly
To place these in random order, first add another column that contains randomly chosen
numbers:
Patrick .73
Penelope .37
Pertinax .16
Polly .48
Then sort the rows according to the values of the random numbers:
Pertinax .16
Penelope .37
Polly .48
Patrick .73
At this point, the original values have been placed in random order; the effect of sorting
the random numbers is to randomize the values associated with them. To rerandomize
the values, choose another set of random numbers, and sort the rows again.
In MySQL, achieve a similar effect by associating a set of random numbers with a query
result and sorting the result by those numbers. To do this, add an ORDER BY RAND() clause:
mysql> SELECT name FROM t ORDER BY RAND();
+----------+
| name |
+----------+
| Pertinax |
| Patrick |
| Polly |