Oracle random row from table

Question

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 1

When you order by dbms_random.value, Oracle orders by the expression, not for a column.For every record Oracle calculate a random number, and then order by this number.

In a similar way, is like this:

select * from emp order by upper(ename);

You have an order by based on a function.

Answer 2

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