Re: Select random rows?

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