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??
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
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.
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;
SELECT
(DBMS_RANDOM.VALUE( 0, 1 )) as RND,
....
....
....
From (your_table)
Order by RND
LIMITin Oracle nor arand()function. Please read the manual