Some of the examples were obtained in this reply from Soen of @Yaakovellis
I warn you that this has not been done performance testing, which can probably be a little tricky if you need to consult a lot of things. I intend to perform own tests and suggest maybe different ways.
Another detail, the use of LIMIT
, TOP 1
, rownum
, etc is only to indicate that there has been limitation of results, after all the intention is to take random results and not just order randomly
Mysql
Select random in Mysql:
SELECT column FROM table
ORDER BY RAND()
LIMIT 1
Postgresql
Select random in Postgresql:
SELECT column FROM table
ORDER BY RANDOM()
LIMIT 1
SQL Server
Select random in Microsoft SQL Server:
SELECT TOP 1 column FROM table
ORDER BY NEWID()
IBM DB2
Random select on IBM DB2
SELECT column, RAND() as IDX
FROM table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Oracle
Random Select in Oracle:
SELECT column FROM
( SELECT column FROM table
ORDER BY dbms_random.value )
WHERE rownum = 1
Sqlite
Select random in Sqlite:
SELECT column FROM table
ORDER BY RANDOM() LIMIT 1
I would very much like to know the reason for the downvotes, after all as far as I understand Answer your own questions is encouraged by the idealized ones of Stack Overflow. Thank those who explain the motivations.
– Guilherme Nascimento
Debate on the question: https://pt.meta.stackoverflow.com/q/7007/3635
– Guilherme Nascimento