## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# 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 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, 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 `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    |
+----------+```

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required