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 |
+------+------------+-----------+---------------------------+-------------+----------------+

Leave a Reply

SELECT N Percent of Records in MySQL