How to request a random row in SQL?

Question

How can I request a random row (or as close to truly random as is possible) in pure SQL?

Answer 1

There is better solution for Oracle instead of using dbms_random.value, while it requires full scan to order rows by dbms_random.value and it is quite slow for large tables.

Use this instead:

SELECT *
FROM employee sample(1)
WHERE rownum=1

Answer 2

ORDER BY NEWID()

takes 7.4 milliseconds

WHERE num_value >= RAND() * (SELECT MAX(num_value) FROM table)

takes 0.0065 milliseconds!

I will definitely go with latter method.

Answer 3

In MSSQL (tested on 11.0.5569) using

SELECT TOP 100 * FROM employee ORDER BY CRYPT_GEN_RANDOM(10)

is significantly faster than

SELECT TOP 100 * FROM employee ORDER BY NEWID()

Answer 4

I have to agree with CD-MaN: Using "ORDER BY RAND()" will work nicely for small tables or when you do your SELECT only a few times.

I also use the "num_value >= RAND() * ..." technique, and if I really want to have random results I have a special "random" column in the table that I update once a day or so. That single UPDATE run will take some time (especially because you'll have to have an index on that column), but it's much faster than creating random numbers for every row each time the select is run.

Answer 5

A simple and efficient way from http://akinas.com/pages/en/blog/mysql_random_row/

SET @i = (SELECT FLOOR(RAND() * COUNT(*)) FROM table); PREPARE get_stmt FROM 'SELECT * FROM table LIMIT ?, 1'; EXECUTE get_stmt USING @i;

Answer 6

For Firebird:

Select FIRST 1 column from table ORDER BY RAND()