sql - Generating Random Number In Each Row In Oracle Query

Question

I want to select all rows of a table followed by a random number between 1 to 9:

select t.*, (select dbms_random.value(1,9) num from dual) as RandomNumber
from myTable t

But the random number is the same from row to row, only different from each run of the query. How do I make the number different from row to row in the same execution?

question from:https://stackoverflow.com/questions/1568630/generating-random-number-in-each-row-in-oracle-query

Answer 1

Something like?

select t.*, round(dbms_random.value() * 8) + 1 from foo t;

Edit: David has pointed out this gives uneven distribution for 1 and 9.

As he points out, the following gives a better distribution:

select t.*, floor(dbms_random.value(1, 10)) from foo t;