how to get random data from a table

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

how to get random data from a table

Postby me_lucky » Mon Feb 25, 2013 6:56 am

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
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: how to get random data from a table

Postby Tim... » Mon Feb 25, 2013 1:55 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: how to get random data from a table

Postby me_lucky » Tue Feb 26, 2013 5:11 am

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
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: how to get random data from a table

Postby Tim... » Tue Feb 26, 2013 10:44 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: how to get random data from a table

Postby me_lucky » Fri Mar 08, 2013 10:47 am

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.
me_lucky
Senior Member
 
Posts: 170
Joined: Tue Jun 02, 2009 11:40 am

Re: how to get random data from a table

Postby Tim... » Fri Mar 08, 2013 11:14 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17940
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest