| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Select random rows?
Steve McDaniels wrote:
>
> 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."
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. --
"Some days you're the pigeon, and some days you're the statue." Received on Thu Nov 11 1999 - 05:13:42 CST
 |
 |