How to Select a Random Sample of Records in MySQL

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">