Selecting a Random Row in Oracle

Question

I need to randomly select values from one table, e.g. tableA.a_id which is a VARCHAR2, and use the value to insert into another table. For instance, assuming three columns needs to be inserted into 100 rows of tableX (a sequence number, a random number between 100 and 999, and values of tableA.a_id):

insert into tableX
select
    rownum,
    dbms_random.value(100,999), 0),
    (select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
    where rownum = 1)
from
   (select level from dual connect by level <= 100);

However, rather than picking a random row from tableA.a_id for each row, it selects the same value for all the rows, e.g.:

1 129 A-ID-48
2 849 A-ID-48
3 367 A-ID-48

However, if I execute the subquery repeatedly, I get a new value each time (for obvious reason), e.g.:

select a_id from 
    (
      SELECT a_id 
      FROM tableA
      ORDER BY dbms_random.value
    )
where rownum = 1;

Result would be after each execution:

A-ID-7
A-ID-48
A-ID-74

How do I alter the original query, or come up with a new one for that matter, that would insertion of random rows from tableA's a_id column for each insert row into the destination table? Desire outcome:

1 129 A-ID-7
2 849 A-ID-48
3 367 A-ID-74

Update 1

Based on mathguy answer, I updated the query for a single table selection:

insert into tableX
select
    rownum,
    round(dbms_random.value(100,999), 0),
    a_id
from
    (
      select 
        round(dbms_random.value(1, (select count(*) from tableA)), 0) tableX_rand_num
      from tableX
    ) x
join 
    (
      select
        a_id, 
        dbms_random.value() rnd,
        rownum tableA_rownum
      from tableA
      order by rnd
    ) a
on x.tableX_rand_num = a.tableA_rownum
where rownum <= 100;

LIMITATION: The number of inserted rows using this method is not going to be independent of number records available in the parent table (tableX). In other word, you can only insert as many as records as the total rows available in tableX. e.g. if tableX has 200 records, and you wish to insert 1000, the query above would only allow you to insert up to 200 rows.

mathguy · Accepted Answer · 2016-08-27T02:29:21.207

5

Make the inner query:

select a_id, dbms_random.value() rnd from tableA order by rnd

and then in the outer query select 100 rows in one shot, with rownum <= 100.

Like so:

insert into tableX
select
    rownum,
    round(dbms_random.value(100,999), 0),
    a_id
from
    (
      SELECT a_id, dbms_random.value() rnd
      FROM tableA
      ORDER BY rnd
    )
where rownum <= 100;

edited Aug 27 '16 at 02:29

answered Aug 27 '16 at 01:47

mathguy

37,873
5
22
47

I'm not quite following your logic. Which inner query do I need to make the replacement for? Don't I need `where rownum = 1` for the first inner query to bring back only one record? – NuCradle Aug 27 '16 at 02:10
It was easier to add the full query to the answer. Please see above. I added back the `round(` which you seem to have deleted by accident. – mathguy Aug 27 '16 at 02:16
I don't think the changes went through. :) – NuCradle Aug 27 '16 at 02:28
Note: This will return 100 distinct rows (which is not entirely "random"). If you want them to be really random, that can be done too, but often people want to draw distinct rows. – mathguy Aug 27 '16 at 02:31
Ok, but what if there are additional tables, e.g. `tableY.y_id`, that the query needs to retrieve random rows from? Like multiple child tables that need to have their primary keys inserted into a parent table at random? Also, `tableA` in this example is much smaller than `tableX`, so duplicates from `tableA.a_id` are expected (same with `tableY.y_id`). – NuCradle Aug 27 '16 at 02:40
These are additional requirements and information not present in your original question. This is not how you should ask for help. With that said: you can select rows from multiple tables the same way. With tableA being much smaller than tableX, you will need to add `row_number()` in tableA, generate random numbers between 1 and count(*) from tableA for tableX, and do a join on these random numbers matching row_number() in the augmented tableA. If you need more help, edit your question or open a new thread. – mathguy Aug 27 '16 at 02:45
I tend to start by removing all the complexities and focus on the core issue. However, at times, when a question is asked, you don't know how a particular answer is going to pigeonhole you into something that you assumed can be easily expanded upon by that solution. Regardless, appreciate your help, and I'll try to see if I can find a way to use multiple disjointed tables with the same solution. – NuCradle Aug 27 '16 at 03:27

Selecting a Random Row in Oracle

1 Answers1

Linked