[sql] Generating Random Number In Each Row In Oracle Query

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?

If you just use round then the two end numbers (1 and 9) will occur less frequently, to get an even distribution of integers between 1 and 9 then:

SELECT MOD(Round(DBMS_RANDOM.Value(1, 99)), 9) + 1 FROM DUAL

At first I thought that this would work:

select DBMS_Random.Value(1,9) output
from   ...

However, this does not generate an even distribution of output values:

select output,
       count(*)
from   (
       select round(dbms_random.value(1,9)) output
       from   dual
       connect by level <= 1000000)
group by output
order by 1

1   62423
2   125302
3   125038
4   125207
5   124892
6   124235
7   124832
8   125514
9   62557

The reasons are pretty obvious I think.

I'd suggest using something like:

floor(dbms_random.value(1,10))

Hence:

select output,
       count(*)
from   (
       select floor(dbms_random.value(1,10)) output
       from   dual
       connect by level <= 1000000)
group by output
order by 1

1   111038
2   110912
3   111155
4   111125
5   111084
6   111328
7   110873
8   111532
9   110953

you don’t need a select … from dual, just write:

SELECT t.*, dbms_random.value(1,9) RandomNumber
  FROM myTable t