How to select 10 percent of records from table?

[maxim]

i tried SAMPLE clause but it works incorrectly

it give me first 9%, next 10%, next 11%, next 9%....

But i need 10% of records randommly everytime

[Sameer]

SELECT * FROM test 
WHERE ROWNUM <= (
                 SELECT CEIL(COUNT(1)*10/100) 
                 FROM test
                )

HTH

Sameer

[stecal]

Previous post does not select 10% randomly - the same rows will be returned each time.

1 SELECT * FROM emp
2 WHERE ROWNUM <= (
3 SELECT CEIL(COUNT(1)*10/100)
4 FROM emp
5* )
SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

SQL> /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

You'll probably have to use a random number generator or package within PL/SQL to achieve randomness. The title of your thread has to do with selecting 10%. The actual question mentions getting the 10% randomly. BIG, BIG difference. What is it you really want?

[Edited by stecal on 09-12-2002 at 06:58 PM]

[tamilselvan]

Use DBMS_RANDOM, a PL/SQL supplied package.