The ability to select a certain percentage of records from a 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 number between 0 and 1, making it easy to select a certain percentage of all records returned from a query. For example, if you want to select approximately 5% of all records, use WHERE RAND() < 0.05
. For approximately 25% of records use WHERE RAND() < 0.25
, and so on.
Here is an example of selecting 1% of all records from the my_data
table. This table contains 1000 records, so this query should return approximately 10.
mysql> SELECT * from my_data WHERE RAND() < .01;
+------+------------+-----------+----------------------------+--------------------+--------+
| id | first_name | last_name | email | country | ip_address |
+------+------------+-----------+---------------------------+-------------+----------------+
| 112 | Bobby | Grant | [email protected] | Indonesia | 34.190.206.98 |
| 272 | Debra | Henry | [email protected] | Japan | 243.60.185.176 |
| 323 | Kathy | Jackson | [email protected] | Panama | 112.15.154.122 |
| 587 | Sara | Ray | [email protected] | Colombia | 44.126.136.74 |
| 609 | Ryan | Stone | [email protected] | Ukraine | 23.41.146.6 |
| 622 | Gregory | Collins | [email protected] | Mexico | 42.185.69.154 |
| 694 | Judith | Warren | [email protected] | China | 48.16.212.62 |
| 752 | Gerald | Powell | [email protected] | Indonesia | 237.8.253.58 |
| 842 | Angela | Little | [email protected] | Afghanistan | 105.86.232.170 |
| 952 | Dennis | Cook | [email protected] | Indonesia | 230.43.148.182 |
+------+------------+-----------+---------------------------+-------------+----------------+