Home » SQL & PL/SQL » SQL & PL/SQL » Select Random Rows
Select Random Rows [message #438829] Tue, 12 January 2010 08:46 Go to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Hi

I have a query which works fine:
SELECT DISTINCT(PCONTACT), PNOTIVAL
                        FROM LCC_TPROBLEM 
                        WHERE ((PSOLVBY = 'WOODWARD_A') OR 
                        (PSOLVBY = 'GOSAI_P') OR 
                        (PSOLVBY = 'BASSETT_A') OR 
                        (PSOLVBY = 'MISTRY_R') OR (
                        PSOLVBY = 'MEADOWS_K') OR 
                        (PSOLVBY = 'SKINNER_R') OR 
                        (PSOLVBY = 'DAVIS_Z') OR 
                        (PSOLVBY = 'BLOUNT_L') OR 
                        (PSOLVBY = 'PATEL_IS') OR
                        (PSOLVBY = 'PATEL_RI') OR 
                        (PSOLVBY = 'THANDI_D') OR 
                        (PSOLVBY = 'FOOT_R'))AND (PNOTIVAL IS NOT NULL) AND
                        (PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
                       ORDER BY PCONTACT


How do I select 20% of the resulting rows randomly?

I can select 250 rows randomly but I need to select 20% as the total number of rows varies each month.

The code I used for selecting 250 random rows is:
SELECT * 
FROM (SELECT DISTINCT(PCONTACT), PNOTIVAL
                        FROM LCC_TPROBLEM 
                        WHERE ((PSOLVBY = 'WOODWARD_A') OR 
                        (PSOLVBY = 'GOSAI_P') OR 
                        (PSOLVBY = 'BASSETT_A') OR 
                        (PSOLVBY = 'MISTRY_R') OR (
                        PSOLVBY = 'MEADOWS_K') OR 
                        (PSOLVBY = 'SKINNER_R') OR 
                        (PSOLVBY = 'DAVIS_Z') OR 
                        (PSOLVBY = 'BLOUNT_L') OR 
                        (PSOLVBY = 'PATEL_IS') OR
                        (PSOLVBY = 'PATEL_RI') OR 
                        (PSOLVBY = 'THANDI_D') OR 
                        (PSOLVBY = 'FOOT_R'))AND (PNOTIVAL IS NOT NULL) AND
                        (PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
                       ORDER BY dbms_random.value)
                       WHERE rownum <=250


Can anyone help

Andy
Re: Select Random Rows [message #438830 is a reply to message #438829] Tue, 12 January 2010 08:48 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many is 20%?

Regards
Michel

[Updated on: Tue, 12 January 2010 08:48]

Report message to a moderator

Re: Select Random Rows [message #438831 is a reply to message #438830] Tue, 12 January 2010 08:52 Go to previous messageGo to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
It varies.
I just need to return 20% of the results of the query in the first code.

Andy
Re: Select Random Rows [message #438832 is a reply to message #438831] Tue, 12 January 2010 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select *
  2  from ( select e.*, ntile(5) over(order by dbms_random.value) n from emp e)
  3  where n = 1
  4  /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO          N
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7844 TURNER     SALESMAN        7698 08/09/1981       1500          0         30          1
      7654 MARTIN     SALESMAN        7698 28/09/1981       1250       1400         30          1
      7839 KING       PRESIDENT            17/11/1981       5000                    10          1

3 rows selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO          N
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7876 ADAMS      CLERK           7788 23/05/1987       1100                    20          1
      7654 MARTIN     SALESMAN        7698 28/09/1981       1250       1400         30          1
      7521 WARD       SALESMAN        7698 22/02/1981       1250        500         30          1

3 rows selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO          N
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980        800                    20          1
      7499 ALLEN      SALESMAN        7698 20/02/1981       1600        300         30          1
      7844 TURNER     SALESMAN        7698 08/09/1981       1500          0         30          1

3 rows selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO          N
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7566 JONES      MANAGER         7839 02/04/1981       2975                    20          1
      7499 ALLEN      SALESMAN        7698 20/02/1981       1600        300         30          1
      7698 BLAKE      MANAGER         7839 01/05/1981       2850                    30          1

3 rows selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO          N
---------- ---------- --------- ---------- ---------- ---------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03/12/1981       3000                    20          1
      7876 ADAMS      CLERK           7788 23/05/1987       1100                    20          1
      7782 CLARK      MANAGER         7839 09/06/1981       2450                    10          1

3 rows selected.

Regards
Michel
Re: Select Random Rows [message #438836 is a reply to message #438829] Tue, 12 January 2010 09:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You could usethe SAMPLE clause of the select - it doesn't provide exactly the specified percentage , but it's pretty quick.
SELECT DISTINCT PCONTACT,PNOTIVAL
FROM   LCC_TPROBLEM 
WHERE ((PSOLVBY = 'WOODWARD_A') OR 
       (PSOLVBY = 'GOSAI_P') OR 
       (PSOLVBY = 'BASSETT_A') OR 
       (PSOLVBY = 'MISTRY_R') OR 
       (PSOLVBY = 'MEADOWS_K') OR 
       (PSOLVBY = 'SKINNER_R') OR 
       (PSOLVBY = 'DAVIS_Z') OR 
       (PSOLVBY = 'BLOUNT_L') OR 
       (PSOLVBY = 'PATEL_IS') OR
       (PSOLVBY = 'PATEL_RI') OR 
       (PSOLVBY = 'THANDI_D') OR 
       (PSOLVBY = 'FOOT_R'))
AND    (PNOTIVAL IS NOT NULL) 
AND    (PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
SAMPLE (20)
ORDER BY PCONTACT


You could also rewrite the query to take up quite a bit less space on screen by using IN instead of OR, like this:
SELECT DISTINCT PCONTACT,PNOTIVAL
FROM   LCC_TPROBLEM 
WHERE  PSOLVBY IN ('WOODWARD_A','GOSAI_P','BASSETT_A','MISTRY_R'
                  ,'MEADOWS_K','SKINNER_R','DAVIS_Z','BLOUNT_L'
                  ,'PATEL_IS','PATEL_RI','THANDI_D','FOOT_R')
AND    PNOTIVAL IS NOT NULL 
AND    PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
               AND     (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
SAMPLE (20)
ORDER BY PCONTACT

icon12.gif  Re: Select Random Rows [message #438838 is a reply to message #438831] Tue, 12 January 2010 09:06 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Perhaps you could try the "SAMPLE" option:
SELECT -- etc --
  FROM LCC_TPROBLEM  
 WHERE -- etc --
SAMPLE (20);



[Updated on: Wed, 20 January 2010 08:47] by Moderator

Report message to a moderator

Re: Select Random Rows [message #438839 is a reply to message #438832] Tue, 12 January 2010 09:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Won't SAMPLE work? After all it is designed to return random set of x % of the total.
Re: Select Random Rows [message #438840 is a reply to message #438838] Tue, 12 January 2010 09:13 Go to previous messageGo to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Thank you all
Tried this:
SELECT DISTINCT (PCONTACT), PNOTIVAL

                        FROM LCC_TPROBLEM 
                        WHERE ((PSOLVBY = 'WOODWARD_A') OR 
                        (PSOLVBY = 'GOSAI_P') OR 
                        (PSOLVBY = 'BASSETT_A') OR 
                        (PSOLVBY = 'MISTRY_R') OR (
                        PSOLVBY = 'MEADOWS_K') OR 
                        (PSOLVBY = 'SKINNER_R') OR 
                        (PSOLVBY = 'DAVIS_Z') OR 
                        (PSOLVBY = 'BLOUNT_L') OR 
                        (PSOLVBY = 'PATEL_IS') OR
                        (PSOLVBY = 'PATEL_RI') OR 
                        (PSOLVBY = 'THANDI_D') OR 
                        (PSOLVBY = 'FOOT_R'))AND (PNOTIVAL IS NOT NULL) AND
                        (PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
                        SAMPLE(20)
                        ORDER BY PCONTACT

But got the error:
ORA-00933: SQL command not properly ended

Andy
icon10.gif  Re: Select Random Rows [message #438842 is a reply to message #438840] Tue, 12 January 2010 09:32 Go to previous messageGo to next message
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
Senior Member
Maybe we got the "SAMPLE" in the wrong place, try this:
SELECT -- etc --
  FROM LCC_TPROBLEM  SAMPLE (20)
 WHERE -- etc --
;

Sad

[Updated on: Tue, 12 January 2010 09:33]

Report message to a moderator

Re: Select Random Rows [message #438843 is a reply to message #438829] Tue, 12 January 2010 09:39 Go to previous messageGo to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
That does it.

Thank you for your expert help

Andy
Re: Select Random Rows [message #438850 is a reply to message #438843] Tue, 12 January 2010 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That does it.

What is "that"?

Regards
Michel

[Updated on: Tue, 12 January 2010 10:37]

Report message to a moderator

Re: Select Random Rows [message #438853 is a reply to message #438850] Tue, 12 January 2010 10:52 Go to previous messageGo to next message
andysam23586
Messages: 17
Registered: September 2009
Junior Member
Sorry folks

What I should of said was that the suggestion worked.
Final code is:
SELECT DISTINCT (PCONTACT), PNOTIVAL

                        FROM LCC_TPROBLEM SAMPLE (20)
                        WHERE ((PSOLVBY = 'WOODWARD_A') OR 
                        (PSOLVBY = 'GOSAI_P') OR 
                        (PSOLVBY = 'BASSETT_A') OR 
                        (PSOLVBY = 'MISTRY_R') OR (
                        PSOLVBY = 'MEADOWS_K') OR 
                        (PSOLVBY = 'SKINNER_R') OR 
                        (PSOLVBY = 'DAVIS_Z') OR 
                        (PSOLVBY = 'BLOUNT_L') OR 
                        (PSOLVBY = 'PATEL_IS') OR
                        (PSOLVBY = 'PATEL_RI') OR 
                        (PSOLVBY = 'THANDI_D') OR 
                        (PSOLVBY = 'FOOT_R'))AND (PNOTIVAL IS NOT NULL) AND
                        (PSOLVAT BETWEEN (TRUNC(ADD_MONTHS(SYSDATE, -1),'MM')-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400)
                        AND (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1)))-TO_DATE('01-JAN-1970','DD-MON-YYYY'))*(86400))
                        
                        ORDER BY PCONTACT

Version of Oracle being used is 10g
Many thanks to all

Andy
Re: Select Random Rows [message #438857 is a reply to message #438853] Tue, 12 January 2010 11:43 Go to previous message
Michel Cadot
Messages: 63807
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course you read the limit of SAMPLE clause:

Quote:
This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table.
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.

SQL> select * from emp sample(20);
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7654 MARTIN     SALESMAN        7698 28/09/1981       1250       1400         30
      7839 KING       PRESIDENT            17/11/1981       5000                    10

2 rows selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23/01/1982       1300                    10

1 row selected.

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17/12/1980        800                    20
      7499 ALLEN      SALESMAN        7698 20/02/1981       1600        300         30
      7934 MILLER     CLERK           7782 23/01/1982       1300                    10

3 rows selected.

SQL> /

no rows selected

SQL> /

no rows selected

SQL> /
     EMPNO ENAME      JOB              MGR HIREDATE          SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09/06/1981       2450                    10
      7839 KING       PRESIDENT            17/11/1981       5000                    10

2 rows selected.

Regards
Michel

[Updated on: Tue, 12 January 2010 11:43]

Report message to a moderator

Previous Topic: Sql to get the value from a string
Next Topic: help in PROCEDURE
Goto Forum:
  


Current Time: Wed Sep 28 10:51:40 CDT 2016

Total time taken to generate the page: 0.15491 seconds