Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: First

Re: First

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 5 Aug 2004 09:15:08 GMT
Message-ID: <slrnch3uks.1to.rene.nyffenegger@zhnt60m34.netarchitects.com>


> Have to confess that I am really disappointed with the responses from
> Michel, Hans, Rene, and Turkbear because not one gave the answer I
> was going to give. And I was sure as I read through theirs that one or
> all of them would have done it first ... so here's mine:
>
> CREATE TABLE t AS
> SELECT object_name
> FROM all_objects
> WHERE rownum < 81;
>
> SELECT COUNT(*)
> FROM t;
>
> SELECT *
> FROM t
> SAMPLE (25);
>
> Returns 25% of the rows.

Daniel, I was under the impression that the OP wanted to retrieve something like 'exactly n rows', not '25% of the rows'. So, if a table has 80 rows, and one wants to select n=20, 25% would be fine. This has at least two problems: first: sample(25) does not select exactly 25%, but the probability for a row to be selected is 25%. Secondly, one has to know how many rows there are in the table prior to selecting it.


RENE_at_ora10> CREATE TABLE t AS
  2 SELECT object_name
  3 FROM all_objects
  4 WHERE rownum < 81;

Table created.

RENE_at_ora10> SELECT COUNT(*) FROM t;

  COUNT(*)


        80

1 row selected.

RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
  2 FROM t
  3 SAMPLE (25);   COUNT(*)


        20

1 row selected.

RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
  2 FROM t
  3 SAMPLE (25);   COUNT(*)


        25

1 row selected.

RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
  2 FROM t
  3 SAMPLE (25);   COUNT(*)


        25

1 row selected.

RENE_at_ora10>
RENE_at_ora10> SELECT count(*)
  2 FROM t
  3 SAMPLE (25);   COUNT(*)


        19

1 row selected.  


Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/
Received on Thu Aug 05 2004 - 04:15:08 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US