Oracle rand() function

Question

I tried to select a random data from table employees with query rand() but it can happen

SELECT email FROM employees
ORDER BY RAND()
LIMIT 1;

and the output is:

ORA-00933: SQL command not properly ended
00933. 00000 - "SQL command not properly ended"

Can somebody tell me why??

Answer 1

To speed-up operations on large table, you might use the SAMPLE clause to randomly extract a sample of data from your table, and then randomly pick one data from that sample:

select email from 
( SELECT email,ROWNUM rn
  FROM employees SAMPLE(5)
  --                    ^
  --      each row has 5% chance of being picked-up
  --      adjust that depending your table size and/or your needs
  ORDER BY dbms_random.value)
where rn = 1;

---

An other idea is that you don't need a full sort just to extract one random row. For example, you might want to try that alternate approach:

with cte as (
    SELECT email, ROWNUM rn
    FROM employees
  ),
  rnd as (
    SELECT TRUNC(DBMS_RANDOM.VALUE(1, (SELECT COUNT(*) FROM CTE))) AS value FROM DUAL
  )

SELECT cte.email FROM cte JOIN rnd
    ON cte.rn = rnd.value;

I don't know if Oracle is able to "properly" optimize such queries though.

Answer 2

The Oracle equivalent of rand() is dbms_random.value.

The Oracle equivalent of limit is either a subquery with rownum or (in Oracle 12) fetch first xx row only. So, one of these should work:

select email
from employees
order by dbms_random.value
fetch first 1 row only;

or:

select email
from (select email
      from employees
      order by dbms_random.value
     ) e
where rownum = 1

Answer 3

Oracle doesn't have LIMIT. You can try this:

select email from 
(SELECT email, row_number() over(order by dbms_random.value) rw FROM employees)
where rw = 1;

Answer 4

SELECT 
(DBMS_RANDOM.VALUE( 0, 1 )) as RND,
....
....
....

From (your_table)

Order by RND