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