Menu
This question asks about getting a random(ish) sample of records on SQL Server and the answer was to use TABLESAMPLE. Is there an equivalent in Oracle 10?
If there isn't, is there a standard way to get a random sample of results from a query set? For example how can one get 1,000 random rows from a query that will return millions normally?
858
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( ).
The function RAND() generates a random value for each row in the table. The ORDER BY clause sorts all rows in the table by the random number generated by the RAND() function. The LIMIT clause picks the first row in the result set sorted randomly.
Below SQL statement is to display the defined number of random rows from a table using RAND() function: Query: SELECT * FROM table_name order by RANDOM() LIMIT n; In table_name mention your Table Name and in the place of 'n' give how many rows to be fetched.
SELECT * FROM ( SELECT * FROM mytable ORDER BY dbms_random.value ) WHERE rownum <= 1000 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
