MySQL Cookbook, 2nd Edition

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 randomization method. Suppose that you have a set of values in a spreadsheet that looks like this:

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, because the effect of sorting the random numbers is to randomize the values associated with them. To re-randomize the values, choose another set of random numbers, and sort the rows again.

In MySQL, you can 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 |
| Penelope |
| Patrick  |
| Polly    |
+----------+
mysql> SELECT name FROM t ORDER BY RAND();
+----------+
| name     |
+----------+
| Patrick  |
| Pertinax |
| Penelope |
| Polly    |
+----------+

Applications for randomizing a set ...