Re: random records

DA Morgan wrote:
> Faby wrote:
> > Hi, I have to select 50 random records from a table. How should i
> > proceed? Should I use "sample", "dbms_random.value"...?
> > Thanks
>
> Use the SAMPLE clause ... that's what it was made for.
>
> SELECT *
> FROM t
> SAMPLE(10);
>
> for example.
>
> Demos in Morgan's Library at www.psoug.org under SELECT STATEMENTS.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Interesting - this looks similar to the sample clause that may be used when updating statistics.
SQL> SELECT *
  2 FROM EMPLOYEE
  3 SAMPLE(10); <snip rows>
180 rows selected.
SQL> Would that not be selecting 10% of the rows in the table?

SELECT *
FROM EMPLOYEE
SAMPLE(10)
WHERE
ROWNUM<11;

<snip rows>
10 rows selected.
SQL> With the addition of ROWNUM<11 your sample is cleaner and easier to understand than mine, as long as there are 100 or more rows in the table. Additionally, it appears that with your method a seed value can be provided to repeatedly produce the same random list.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Jul 12 2006 - 15:21:38 CDT