Menu
I need to select rows randomly from an Oracle DB.
Ex: Assume a table with 100 rows, how I can randomly return 20 of those records from the entire 100 rows.
556
SELECT columns FROM table ORDER BY RAND() LIMIT n; The RAND() function generates a random number between 0 and 1 for each row in the table and the ORDER BY clause will order the rows by their random number.
To get a single row randomly, we can use the LIMIT Clause and set to only one row. ORDER BY clause in the query is used to order the row(s) randomly. It is exactly the same as MYSQL. Just replace RAND( ) with RANDOM( ).
Using the main menu, select Tools->Database Export. An Export wizard will open. At the top of the screen, enter a directory and file name.
SELECT * FROM ( SELECT * FROM table ORDER BY DBMS_RANDOM.RANDOM) WHERE rownum < 21; SAMPLE() is not guaranteed to give you exactly 20 rows, but might be suitable (and may perform significantly better than a full query + sort-by-random for large tables):
SELECT * FROM table SAMPLE(20); Note: the 20 here is an approximate percentage, not the number of rows desired. In this case, since you have 100 rows, to get approximately 20 rows you ask for a 20% sample.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
