where do you get
random.initialize...
(I'm on 7.3.2)
Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
news:3822BDD1.2972_at_yahoo.com...
> fumi wrote:
> >
> > Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message
news:382169BC.71E8_at_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_at_yahoo.com
>
> "Some days you're the pigeon, and some days you're the statue."
Received on Wed Nov 10 1999 - 17:19:21 CST
