Oracle Sort records randomly
Sorting records in Oracle is not a problem thanks to the ORDER BY statement.
However, if you do not want to sort the data by a certain column, but want to query the data records in a random order, you can pass the Oracle function dbms_random.value to the ORDER BY clause.
This function returns a random number after which it is sorted at the end.
Example over an entire table:
|
1 2 3 |
SELECT * FROM mytable ORDER BY dbms_random.value; |
Example that returns a certain number of records:
|
1 2 3 4 5 |
select * from ( SELECT * FROM mytable ORDER BY dbms_random.value )where rownum <= 5; |
In this example, five random records are fetched from the table. The rownum column was used for this.
Since Oracle always runs the WHERE condition before ORDER BY, we need an innerselect so that the same 5 records are not always returned mixed.
