0

I want to take random sample by id (not by data points) from Oracle database. My code is shown below but it failed.

select C.* from original_table C, 
(select * from (select id from original_table group by id) as A 
ORDER BY RAND() LIMIT 500) as B where C.id = B.id;

error message is ORA-00907:missing right parenthesis, highlighting the "as"s and the parenthesis "(select * " and "LIMIT 500) ".

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
ChiefsCreation
  • 389
  • 1
  • 3
  • 10

1 Answers1

1

Two things:

  • RAND() is not an Oracle function.

You could use dbms_random package.

  • LIMIT is not supported in Oracle.

Alternatively, you could use following:

  1. On 12c, you could use the new Top-n row limiting feature.
  2. ROWNUM in pre-12c version..
Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124