Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SAMPLE and SEED clauses

585312Jun 27 2007 — edited Jun 27 2007
Hi,
SQL user, on 10g
Anyone can help me with the following? Trying to select a true random sample from a list.

1) If I enter the following, I get an every changing number of records returned (doesn't really suit my needs since I need a set number)

SELECT *
FROM mytable SAMPLE(10)
WHERE id IS NOT NULL;

2) If I enter the following, and would like to maximize the number of records returned to 100, I get....nothing.

SELECT *
FROM mytable SAMPLE(10) SEED(100)
WHERE id IS NOT NULL;

3) If I enter the following, I get a 100 records returned, a different set of records every time, but I have been told it is not a truly random selection of 100 records.

SELECT *
FROM mytable SAMPLE(10) SEED(100)
WHERE id IS NOT NULL
and ROWNUM<=100;

HELP! Anyone can help with the SEED clause and what I am doing wrong, and what the proper way to return a truly random number of n records.
Thanks
Isa
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 25 2007
Added on Jun 27 2007
12 comments
6,281 views