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

User · Accepted Answer

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

User · Answer

you don   t need a select     from dual  just write   SELECT t    dbms random value 1 9  RandomNumber   FROM myTable t

User · Answer

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  lt   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  lt   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

User · Answer

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

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

Examples related to sql

Examples related to oracle