SQL – Select a random row / record

I've seen many posts on this topic, but none of the seem to account for the fact that your primary key may not start at the number 1.

In your finds, you may have come across the following method, which works and is efficient, if your primary key begins with 1.

SELECT question_id, question_name
FROM question
WHERE question_id >= (SELECT FLOOR(MAX(question_id)  * RAND()) FROM question)
LIMIT 1

The method below will "detect" a primary key which may start at any integer.

SELECT question_id, question_name
FROM question
WHERE question_id >= (SELECT FLOOR((MAX(question_id) - MIN(question_id) + 1) * RAND()) + MIN(question_id) FROM question)
ORDER BY question_id
LIMIT 1

This may seem obvious, but as a further optimization, consider using a constant in place of MAX(id) This method only makes sense if you are using a table with a fixed number of rows (records are never or rarely changed.) Using the original query as an example:

SELECT question_id, question_name
FROM question
WHERE question_id >= (SELECT FLOOR(30000  * RAND()) FROM question)
LIMIT 1

Or, if you find that your scripting languages random number function is faster than your databases's, simply generate the random number and multiply it by your row number constant directly in your script. Then, pass that number to your query.

Subscribe

  • follow us in feedly


This entry was posted in SQL / Database. Bookmark the permalink.

Updated: 2012-03-04

Phil LaNasa