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?

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; 

Answer 2

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