SQL

Select random records


Problem

Most records are sorted by date or alphanumerical, but there are cases where you want to show random records. Maybe to give each entry a fair chance of being seen.



Solution

Assuming we've got a table called persons like this:

id title
1 Bob
2 Joe
3 Jeff

We can now query this table with this to get a randomly sorted result.

SELECT id, title FROM persons ORDER BY rand()

If you execute this query multiple times you'll notice how the order changes. However, please be aware that this query will assign a random value to every single record. If you have lots of rows, this will eventually become slow.

In case you just want one random row, there's a more efficient way. The query looks a bit ugly and should only be used with care:

SELECT * FROM persons
WHERE RAND() < (SELECT 1/COUNT(*) * 2 FROM persons) 
ORDER BY RAND()
LIMIT 1

Instead of just sorting the data, we are also filtering. Assuming we want one row of a table with 100'000 rows we can calculate the fragmentation. 1 / 100000 = 0.00001. Probability tell us, that we just have to pick the one row where rand() is smaller or equal than this value. However, randomness also isn't perfectly distributed which means that in some cases we won't get a result at all. We can avoid that by simply increasing the fragmentation. In the query above we have a factor of 2 which for a large table would be a lot and thus mostly be used for small tables. But even with a factor of 2, there's a chance you won't get a result back at all. Make sure you are okay with that case before you use this approach.

Comments




Please sign-in to post a comment