How to select 10 percent of records from table?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to select 10 percent of records from table?

  1. #1
    Join Date
    Feb 2001
    Posts
    35


    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

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Code:
    SELECT * FROM test 
    WHERE ROWNUM <= (
                     SELECT CEIL(COUNT(1)*10/100) 
                     FROM test
                    )
    HTH

    Sameer

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    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]

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Use DBMS_RANDOM, a PL/SQL supplied package.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width