Fetch a random 5 rows from the database

Dani 4 Tallied Votes 581 Views Share

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:

FROM table

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:

FROM students

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 :)

Assembly Guy 72 Posting Whiz

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.

pritaeas 2,179 ¯\_(ツ)_/¯ Moderator Featured Poster

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).

Dani 3,971 The Queen of DaniWeb Administrator Featured Poster Premium Member

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.

pritaeas 2,179 ¯\_(ツ)_/¯ Moderator Featured Poster

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`
abubaker_2 0 Newbie Poster

thank you dani for sharing :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.