Oracle random row from table

oracle random row
Adrian picture Adrian · Oct 13, 2011 · Viewed 15.1k times · Source

I found this solution for selecting a random row from a table in Oracle. Actually sorting rows in a random manner, but you can fetch only the first row for a random result.

SELECT *
FROM table
ORDER BY dbms_random.value;

I just don't understand how it works. After ORDER BY it should be a column used for sorting. I see that "dbms_random.value" returns a value lower than zero. This behavior can be explained or is just like that?

Thanks

Answer

Kevin Burton picture Kevin Burton · Oct 13, 2011

you could also think of it like this:

SELECT col1, col2, dbms_random.value
FROM table
ORDER BY 3

In this example the number 3 = the third column

Related questions

How to get records randomly from the oracle database?

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.

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More