You want to randomize a set of rows or values.
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
BY clause to the query.
The technique is roughly equivalent to a spreadsheet randomization
method. Suppose you have a set of values in a spreadsheet that looks
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, a similar effect is achieved by associating a set of random
numbers with a query result and sorting the result by those numbers.
For MySQL 3.23.2 and up, this is done with an
RAND( ) clause:
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 | +----------+