how to get random data from a table

[me_lucky]

Dear Team,

I have a table called "X" (Master data table), based upon some condition i'l upload another table called "Y" (Child table) with the data (selected columns only) from table "X".

I would like to select once row from table "X" randomly and update table "Y". I'l do this for N no of times, all the time i should select unique data from table "X".

I request you all, pl help me with sample script.

Thanks & Regards,

Lucky

[Tim...]

Hi.

I think you should select your rows using the SAMPLE clause, which will give you a random sample of rows. The following will give a random sample of 10% of the rows in the table.

Code: Select all

select *
from emp
sample (10);

Cheers

Tim...

[me_lucky]

Dear Tim,

Thanks for the update.

With the provided code, i tried but it will not work for my situation.
Suppose my table is having 20 records, if i give sample(5) sometimes its retriving the data and sometimes its not. though its retriving the data more than one column.
I just want to get one recode for every ittration. Like how the DBMS_RANDOM function will work, all the time it should generate random data.

Pl advice.

Thanks & Regards,
Lucky

[Tim...]

Hi.

When using SAMPLE against such small tables you are going to get discrepancies. It is not designed for such small sets.

You can certainly use DBMS_RANDOM in combination with ROWNUM to get random rows, but you will not be able to guarantee not getting the same row back again, unless you specifically code to prevent it, by remembering what rows you've previously returned. You will also need to know exactly how many rows you have before you start, so you can use DBMS_RANDOM properly. This sounds like a really strange requirement...

Cheers

Tim...

[me_lucky]

Hi Tim,

Thanks for the information provided.
I am able to achieve this by using random number against rownum. I know its strange requirment, but it is for some testing, we need to create data based on existing tables data.

Thanks again,
Lucky.

[Tim...]