The ability to select a random sample of records from query or database table can be important when working with lots of data. Luckily this is easy to do in MySQL with the RAND() function and a WHERE clause.

RAND() returns a random floating point value between 0 and 1, making it very easy to select a certain percentage of all records returned from a query. For example, if you want to select approximately 10% of all records, use WHERE RAND() < 0.1. For approximately 20% of records use WHERE RAND() < 0.2, and so on.

Here is an example of randomly sampling 2% of records from the sample_data table. This table contains 1000 records, so this query should return approximately 20.

mysql> SELECT * from sample_data WHERE RAND() < .02;
+------+------------+-----------+----------------------------+--------------------+-----------------+
| id   | first_name | last_name | email                      | country            | ip_address      |
+------+------------+-----------+----------------------------+--------------------+-----------------+
|   46 | Amy        | Little    | [email protected]        | Philippines        | 124.33.72.233   |
|  116 | Steve      | Duncan    | [email protected]         | China              | 138.33.229.153  |
|  168 | Marie      | Franklin  | [email protected]   | Brazil             | 133.172.154.237 |
|  184 | Joshua     | Harris    | [email protected]   | Russia             | 174.236.125.192 |
|  187 | Todd       | Henry     | [email protected]          | France             | 70.153.4.105    |
|  208 | Peter      | Turner    | [email protected]          | South Korea        | 100.224.89.44   |
|  268 | Kathy      | Willis    | [email protected]       | Dominican Republic | 193.30.107.225  |
|  331 | Stephanie  | Gray      | [email protected]          | Kosovo             | 137.5.225.171   |
|  336 | Paula      | Ferguson  | [email protected]     | Philippines        | 215.4.174.18    |
|  366 | Aaron      | Ramirez   | [email protected]  | Indonesia          | 0.84.149.239    |
|  409 | Diana      | Watkins   | [email protected]      | Nigeria            | 97.234.54.72    |
|  456 | Paula      | Wells     | [email protected]         | United States      | 29.185.158.87   |
|  475 | David      | Oliver    | [email protected]       | Bangladesh         | 67.181.88.90    |
|  480 | Howard     | Torres    | [email protected]       | Russia             | 5.95.245.76     |
|  520 | Edward     | Richards  | [email protected] | China              | 244.228.180.17  |
|  733 | Angela     | Gardner   | [email protected]       | Sweden             | 209.74.203.239  |
|  741 | Fred       | Turner    | [email protected]   | Russia             | 210.143.233.68  |
|  746 | Janice     | Stone     | [email protected]     | Sweden             | 239.173.103.91  |
|  770 | Norma      | Griffin   | ngr[email protected]      | Paraguay           | 34.31.185.139   |
|  779 | Nancy      | Marshall  | [email protected]   | Indonesia          | 111.214.200.217 |
|  846 | Jack       | Turner    | [email protected]        | Philippines        | 181.85.170.207  |
|  875 | Carlos     | Payne     | [email protected]     | Japan              | 90.29.149.92    |
+------+------------+-----------+----------------------------+--------------------+-----------------+

Leave a Reply

How to Select a Random Sample of Records in MySQL