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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.