RE: Random Select of X % of total records

Thank you all.  

OK. Which version did they put sample in .  

Whatever happened to the good old days of Oracle 6.

-----Original Message-----

From: Justin Cave (DDBC) [mailto:jcave_at_ddbcinc.com] Sent: Friday, October 07, 2005 3:12 PM
To: klange_at_ppoone.com; oracle-l
Subject: RE: Random Select of X % of total records

Assuming the desire is to see roughly 10% of the CLAIMS table and you don't have a particularly strict definition of "random", you should be able to just use the SAMPLE clause  

SCOTT @ hp92 Local> create table t as select * from all_objects;  

Table created.  

Elapsed: 00:00:04.23

SCOTT @ hp92 Local> select count(*) from t;  

  COUNT(*)

     29907  

Elapsed: 00:00:00.54

SCOTT @ hp92 Local> select count(*) from t sample(10);  

  COUNT(*)

      2989  

Elapsed: 00:00:00.08  

Justin Cave <jcave_at_ddbcinc.com>

Distributed Database Consulting, Inc.

http://www.ddbcinc.com  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kevin Lange
Sent: Friday, October 07, 2005 4:07 PM
To: oracle-l
Subject: Random Select of X % of total records  

Evening all;

  One of our developers is asking if there is a simple SQL way to generate a random select of the total records in a table. i.e. They want to see 10% of the records in the CLAIMS table.  

Database in question is 9i (9.2.0.6)  

SQL has to be simple because its going to be used by Cognos which does not allow anonymous sql blocks.  

I was hoping that some of the new analytical functions would work for this but I have not used them yet.  

Any help out there ??

--

http://www.freelists.org/webpage/oracle-l Received on Fri Oct 07 2005 - 15:24:08 CDT