Generating Random Number in Each Row in Oracle Query

Generating Random Number In Each Row In Oracle Query

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;

Generate a random number for each group and assign it to all rows in the group

If the random number can be sequential, you can use dense_rank():

select t.*, dense_rank() over (order by id) as group_num
from t;

Or for a bit more randomness:

select t.*,
       dense_rank() over (order by farm_fingerprint(cast(id as string)), id) as group_num
from t;

Alternatively, a separate calculation by id might be simplest:

select *
from t join
     (select id,
             dense_rank() over (order by rand()) as group_num
      from t
      group by id
     ) tt
     using (id)

Random Numbers of a column of a table

There is not a lot of detail in your question, but this may help you down a path.

The select below gets a random value between 0 and 500. I put the round function in so that it's whole numbers.

select round(dbms_random.value(0,500)) val
from dual
where round(dbms_random.value(0,500)) not in (select MI_COLUMNA from MI_TABLA);

If the random number chooses a value that is in your column, it will not return anything. Therefor you may want to put it in a loop until you get a number.

insert a random number into each row in table

Just not use subquery:

update lovalarm
set lovsiteid = TRUNC(dbms_random.value(14300,17300))

Oracle SQL - Generate and update a column with different random number in every row

Looks like sub-query is the problem.

This seems to be working-

update mytable r set r.generated_num = TRUNC(dbms_random.value(1,9999999))

---

Related Topics