Rene Nyffenegger wrote:
>>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.
>
>
> -------------------------------------------
> ---- Demonstrating that sample does ----
> ---- not always return the same number ----
> ---- of rows. ----
> -------------------------------------------
>
> 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
You are correct. I was just amazed that the SAMPLE clause wasn't
mentioned as a possible approach. It seemed to me that it met the
OP's requirement ... but who can say.
Thanks for correcting any lack of clarity in what I posted.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Thu Aug 05 2004 - 08:13:50 CDT
