4

Last night, a friend mentioned to me that one day he was trying to fetch a random 5 rows from a MySQL database. He researched online, and was eventually presented with a working solution in which he would generate five random numbers. Then, he would query the database for items whose ID number matched the numbers. If an item didn't exist (because it had been deleted, etc.) then he would randomly generate a different number and fetch again. He would continuously do this in a loop until he had five unique rows fetched from the database.

I was caught off guard, to say the least, that this is the method he was told to use online. For anyone else having this problem, it can all be done from a very simple query:

SELECT *
FROM table
ORDER BY RAND()
LIMIT 5

The above query will randomly select five unique rows from the table. Of course, you can use WHERE clauses and such to narrow down the possibilities. You'll receive up to five rows but potentially less if there are fewer than 5 that match the criteria passed into the MySQL query.

Another interesting thing that you can do is order by multiple fields. For example, suppose you have a table of 120 students with columns for their name and grade: thirty 9th graders, thirty 10th graders, thirty 11th graders, and thirty 12th graders.

Suppose we were to execute the following query:

SELECT *
FROM students
ORDER BY grade DESC, RAND()
LIMIT 50

Basically here we're going to first sort by grade in descending order, and then among those within the same grade, we're going to put them in random order. What we will end up with is a result set of 50 students in the following order: 30 12th graders in a random order followed by 20 11th graders in a random order.

Hope someone found this useful :)

While pursuing a Computer Science degree, I founded DaniWeb.com, an online community for developers and IT professionals. I coded the backend platform from the ground up and I also do all of the advertising sales and SEO. I'm a super-geeky programmer with a passion for Internet marketing.

4
Contributors
5
Replies
84
Views
4 Years
Discussion Span
Last Post by abubaker_2
0

Nice. Years ago I was writing some PHP and I used a crappy method whereby I selected all rows, buffered them into an array, then selected some random elements from the array... it was horrible. It took me a lot of searching back then to find the same method that you've used here.

Good to see someone's spreading the better, cleaner solution.

0

Depending on the size of your table, it's sometimes faster to first select 5 random ID's (because it uses the index table), and then join with the actual columns you need (or get them in a second call).

Edited by pritaeas

0

Prit, I wonder if your suggestion is what my friend read somewhere on the web, and then misunderstood to mean to generate random IDs in PHP. However, where he went wrong was that not every ID in the database was sequentially numbered, so sometimes he would generate a random number "ID" that doesn't correlate to a row in the database, and then use a loop to randomly generate a new number and try again.

2

It's possible. You need a query to select the 5 randoms, you shouldn't do this in PHP for exactly that reason.

Untested, but something like this self-join should work:

SELECT `t2`.* FROM `table` `t1`
LEFT JOIN `table` `t2` ON `t1`.`id` = `t2`.`id`
ORDER BY RAND() LIMIT 5
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.