How do I select a certain percentage of records 1

[ynnepztem]

I have a table of 120 records (of course this number will change) I need to select a random 5%. There is an ID field that contains the record number and I thought if I could select every sixth record I would be OK. I am very new to SQL and don't know how to do this. (I already have a routine that counts the records and divides them by the percentage.)

Thanks

Penny

 

[carp]

If the records don't have to be TOO random and the ID field is numeric and sequential in number, then you could get approximately five percent with

SELECT *
FROM my_table
WHERE MOD(id_field,20) = 0;

Otherwise, you could fake it with
select whatever_you_want from
(select rownum row_num, whatever_you_want from my_table) x
where mod(row_num, 20) = 0;

These are crude and not truly random, but might do the job without too much brain damage.

 

[karluk]

If you are using Oracle 8i, you can use the "sample" option. I doubt that this would work in other dbms's, though.

select * from your_table sample 5;

 

[carp]

Hmmm. Very interesting - this was a new one on me. However, I find the syntax is actually
SELECT * FROM your_table sample(5); <Note Parenthesis>

Also something to bear in mind: this appears to only work on tables. If you try to do this on a view, the SQL runs, but you get EVERYTHING (not the sample you requested) returned!

Thanks, Karluk!

 

[carp]

Also noted in the documentation - this won't work when a join is involved. Unlike trying to use SAMPLE on a view (where the statement executes but the SAMPLE clause is simply ignored), trying to use SAMPLE with a join results in an ORA-30561 error!

 

[ynnepztem]

Thanks to everyone who responded. I guess I left out an important item. I am using SQL Server 2000. Neither the MOD or SAMPLE was accepted.
When I used

SELECT * FROM ss4 sample(5);
I got back the following

Index ID 5 on table 'ss4' (specified in the FROM clause) does not exist.

When I used

SELECT *
FROM my_table
WHERE MOD(ID,20) = 0;

I got back

'MOD' is not a recognized function name.

Any more suggestions? Or what do I do to make SAMPLE work. I looked it up in HELP but nothing was there.

 

[karluk]

Please read thread183-34613. It discusses the syntax for selecting every nth row if you're lucky enough to have an appropriate numeric column defined in your table. The strategy is the same as Carp's, but the syntax is correct for SQL Server.

By the way, if you want a 5% sample, you would select one out of every 20 rows, not one out of every six.