Recipe 13.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 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 of rows include any scenario that uses selection without replacement (choosing each item from a set of items until there are no more items left). Some examples of this are:

Determining the starting order for participants in an event. List the participants in a table, and select them in random order.
Assigning starting lanes or gates to participants in a race. List the lanes in a table, and select a random lane order.
Choosing the order in which to present a set of quiz questions.
Shuffling a deck of cards. Represent each card by a row in a table, and shuffle the deck by selecting the rows in random order. Deal them one by one until the deck is exhausted.

To use the last example as an illustration, let's implement a card deck-shuffling algorithm. Shuffling and dealing cards is randomization plus selection without replacement: each card is dealt once before any is dealt twice; when the deck is used up, it is reshuffled to rerandomize it for a new dealing order. Within a program, this task can be performed with MySQL using a table deck that has 52 rows, assuming a set of cards with each combination of 13 face values and 4 suits:

Select the entire table, and store it into an array.
Each time a card is needed, take the next element from the array.
When the array is exhausted, all the cards have been dealt. "Reshuffle" the table to generate a new card order.

Setting up the deck table is a tedious task if you insert the 52 card records by writing all the INSERT statements manually. The deck contents can be generated more easily in combinatorial fashion within a program by generating each pairing of face value with suit. Here's some PHP code that creates a deck table with face and suit columns, and then populates the table using nested loops to generate the pairings for the INSERT statements:

$result =& $conn->query ("               CREATE TABLE deck               (                 face  ENUM('A', 'K', 'Q', 'J', '10', '9', '8',                            '7', '6', '5', '4', '3', '2') NOT NULL,                 suit  ENUM('hearts', 'diamonds', 'clubs', 'spades') NOT NULL               )"); if (PEAR::isError ($result))   die ("Cannot issue CREATE TABLE statement\n"); $face_array = array ("A", "K", "Q", "J", "10", "9", "8",                      "7", "6", "5", "4", "3", "2"); $suit_array = array ("hearts", "diamonds", "clubs", "spades"); # insert a "card" into the deck for each combination of suit and face $stmt =& $conn->prepare ("INSERT INTO deck (face,suit) VALUES(?,?)"); if (PEAR::isError ($stmt))   die ("Cannot insert card into deck\n"); foreach ($face_array as $index => $face) {   foreach ($suit_array as $index2 => $suit)   {     $result =& $conn->execute ($stmt, array ($face, $suit));     if (PEAR::isError ($result))       die ("Cannot insert card into deck\n");   } }

Shuffling the cards is a matter of issuing this statement:

SELECT face, suit FROM deck ORDER BY RAND();

To do that and store the results in an array within a script, write a shuffle_deck⁠(⁠ ⁠ ⁠) function that issues the query and returns the resulting values in an array (again shown in PHP):

function shuffle_deck ($conn) {   $result =& $conn->query ("SELECT face, suit FROM deck ORDER BY RAND()");   if (PEAR::isError ($result))     die ("Cannot retrieve cards from deck\n");   $card = array ();   while ($obj =& $result->fetchRow (DB_FETCHMODE_OBJECT))     $card[] = $obj;   # add card record to end of $card array   $result->free ();   return ($card); }

Deal the cards by keeping a counter that ranges from 0 to 51 to indicate which card to select. When the counter reaches 52, the deck is exhausted and should be shuffled again.