• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Randomly ordering a MySQL result set

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.

Check Out These Related posts:

  1. An alternative to ORDER BY RAND() for MySQL
  2. MySQL’s LIMIT syntax can be slow for large offsets
  3. MySQL utility commands
  4. Randomly ordering data with MySQL with a random value column

Filed Under: MySql

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap