Xref: alice comp.databases.oracle.misc:46634 comp.databases.oracle.server:73586
Path: alice!news-feed.fnsi.net!newsfeed.icl.net!diablo.theplanet.net!news.theplanet.net!newspost.theplanet.net!not-for-mail
From: Connor McDonald <connor_mcdonald@yahoo.com>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server
Subject: Re: Select random rows?
Date: Thu, 11 Nov 1999 19:13:42 +0800
Organization: Customer of Planet Online
Lines: 173
Message-ID: <382AA4E6.64D6@yahoo.com>
References: <3821A42D.6E53D76@fast.no> <382169BC.71E8@yahoo.com> <7vtdac$6j4$9@news.seed.net.tw> <3822BDD1.2972@yahoo.com> <80cujf$f84$1@plo.sierra.com>
Reply-To: connor_mcdonald@yahoo.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: news6.svr.pol.co.uk 942347268 6542 62.136.188.215 (11 Nov 1999 19:07:48 GMT)
NNTP-Posting-Date: 11 Nov 1999 19:07:48 GMT
X-Complaints-To: abuse@theplanet.net
X-Mailer: Mozilla 3.01 (Win95; I)
To: Steve McDaniels <steve.mcdaniels@sierra.com>

Steve McDaniels wrote:
> 
> where do you get
> 
> random.initialize...
> 
> (I'm on 7.3.2)
> 
> Connor McDonald <connor_mcdonald@yahoo.com> wrote in message
> news:3822BDD1.2972@yahoo.com...
> > fumi wrote:
> > >
> > > Connor McDonald <connor_mcdonald@yahoo.com> wrote in message
> news:382169BC.71E8@yahoo.com...
> > > > kev wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > How do I select, say, 10 random rows from a dataset?
> > > > >
> > > > > - Kev
> > > >
> > > > (if your table is not too large...)
> > > >
> > > > select *
> > > > from ( select rownum each_row, a.*
> > > >        from table a )
> > > > where each_row = xxx
> > > > and rownum < 10
> > > >
> > > > where 'xxx' is whatever appropriate random number generator you want
> > > > (eg  dbms_random)
> > >
> > > This can not work.
> > > In Oracle8i, to random select, say, 5 random records from a table:
> > >
> > > SQL> execute dbms_random.initialize(dbms_utility.get_time());
> > >
> > > PL/SQL procedure successfully completed.
> > >
> > > SQL> select * from
> > >   2      (select * from emp order by dbms_random.random())
> > >   3    where rownum<=5;
> > >
> > >     EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM
> DEPTNO
> >
> > --------- ---------- --------- --------- --------- --------- --------- ---
> ------
> > >      7934 MILLER     CLERK          7782 23-JAN-82      1300
> 10
> > >      7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300
> 30
> > >      7698 BLAKE      MANAGER        7839 01-MAY-81      2850
> 30
> > >      7876 ADAMS      CLERK          7788 13-JUL-87      1100
> 20
> > >      7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0
> 30
> > >
> > > SQL> /
> > >
> > >     EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM
> DEPTNO
> >
> > --------- ---------- --------- --------- --------- --------- --------- ---
> ------
> > >      7876 ADAMS      CLERK          7788 13-JUL-87      1100
> 20
> > >      7788 SCOTT      ANALYST        7566 13-JUL-87      3000
> 20
> > >      7782 CLARK      MANAGER        7839 09-JUN-81      2450
> 10
> > >      7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0
> 30
> > >      7654 MARTIN     SALESMAN       7698 28-SEP-81      1250      1400
> 30
> > >
> > > SQL> /
> > >
> > >     EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM
> DEPTNO
> >
> > --------- ---------- --------- --------- --------- --------- --------- ---
> ------
> > >      7499 ALLEN      SALESMAN       7698 20-FEB-81      1600       300
> 30
> > >      7566 JONES      MANAGER        7839 02-APR-81      2975
> 20
> > >      7839 KING       PRESIDENT           17-NOV-81      5000
> 10
> > >      7844 TURNER     SALESMAN       7698 08-SEP-81      1500         0
> 30
> > >      7782 CLARK      MANAGER        7839 09-JUN-81      2450
> 10
> > >
> > > SQL> /
> > >
> > >     EMPNO ENAME      JOB             MGR HIREDATE        SAL      COMM
> DEPTNO
> >
> > --------- ---------- --------- --------- --------- --------- --------- ---
> ------
> > >      7900 JAMES      CLERK          7698 03-DEC-81       950
> 30
> > >      7521 WARD       SALESMAN       7698 22-FEB-81      1250       500
> 30
> > >      7902 FORD       ANALYST        7566 03-DEC-81      3000
> 20
> > >      7566 JONES      MANAGER        7839 02-APR-81      2975
> 20
> > >      7698 BLAKE      MANAGER        7839 01-MAY-81      2850
> 30
> >
> > Typo on my part...
> >
> > The following WILL work (and you don't have to be on 8i to do it)...
> >
> > SQL> create table test1 ( x number );
> >
> > Table created.
> >
> > SQL> insert into test1 select rownum from all_objects
> >   2  where rownum < 999;
> >
> > 998 rows created.
> >
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL> select *
> >   2  from ( select rownum xxx, a.* from test1 a)
> >   3  where xxx in ( select random.rndint(500)
> >   4                 from test1
> >   5                 where rownum < 10 )
> >   6  /
> >
> >       XXX         X
> > --------- ---------
> >        15        15
> >        69        69
> >       133       133
> >       160       160
> >       167       167
> >       221       221
> >       338       338
> >       350       350
> >       450       450
> >
> > 9 rows selected.
> >
> > SQL>
> > --
> > ===========================================
> > Connor McDonald
> > "These views mine, no-one elses etc etc"
> > connor_mcdonald@yahoo.com
> >
> > "Some days you're the pigeon, and some days you're the statue."

dbms_random comes along with 8...

You can write your own with little difficulty...

Check www.nr.com for a simple means of creating one.
-- 
===========================================
Connor McDonald
"These views mine, no-one elses etc etc"
connor_mcdonald@yahoo.com

"Some days you're the pigeon, and some days you're the statue."