Randomly ordering a MySQL result set

There are times when you might need to randomly order a MySQL resultset, for example when choosing some winners at random for a prize draw. This post looks at how you would do this and the next MySQL post will look at "randomly" ordering data across multiple pages.

Using the prize draw example, let's say we conducted a survey and were going to give a prize to 10 of the people who completed the survey. These people are to be chosen at random. The table is called "survey" and we want their contact details.

To select 10 records from the table in random order, do the following:

SELECT first_name, last_name, email_address, phone
FROM survey
ORDER BY RAND()
LIMIT 10

The ORDER BY RAND() line is what makes the resultset random, and the LIMIT 10 line returns just the first 10 results.

Ordering a resultset randomly in this way is inefficient and will be slow on large resultsets, because the random number has to be applied to every row before it's sorted. For the purposes of choosing a winner or similar sorts of circumstances it's great but it's not that useful if you need to page "random" results over several pages.

If you do need to page data "randomly" using RAND() is not much use because the same records will appear on multiple pages, and you won't get a consistent data set as you page back and forward through the same pages. I will look at how to do this in next week's MySQL post. Make sure to subscribe to my RSS feed (details below) so you don't miss out.

Related posts:

• An alternative to ORDER BY RAND() for MySQL
• Rounding numbers with MySQL
• "Randomly" order data across multiple pages with MySQL
• Using "group by" and "having" with MySQL
• Find the length of the longest string in MySQL