Select random records

Hi Experts,

I have this piece of SQL script which I use to select a random sample of 5% of records from the whole data set.

I've looked in the Aginity boards and documentation but I can't find how to select say random sample of 200 records rather than percentage.

SELECT * 
FROM   table_a
WHERE  Random() <= 0.05; 

Open in new window


Thanks,

OS
onesegunAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
If you are looking for a specific number of records and are going to use a RANDOM function in conjunction with LIMIT, then be sure that the percentage you specify will give you significantly more records than you need.  Especially if the table has a small number of rows.

You may be better off with something like this:

select * from table_a order by random() limit 200;
0
 
Guru JiCommented:
you can do this

  SELECT TOP 200 *
  FROM table_a
  ORDER BY NEWID()

or

 SELECT TOP 10 PERCENT *
  FROM table_a
  ORDER BY NEWID()
0
 
ramromconsultant Commented:
what randomness test must the subset pass?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
johnsoneSenior Oracle DBACommented:
Is this a one time exercise or would the be done multiple times?

If the tables you are selecting from are distributed on random, then you have a sort of built in randomness to the data.  You can then use a query like this:

select * from table_a where datasliceid=5 limit 200;

You can use any number for the data slice, but the records would tend to be the same ones if you used the same dataslice repeatedly.  They wouldn't always be the same, but it would be close.

You could also use a method where you do something like this:

create table table_a_random as select * from table_a distribute on random;
select * from table_a where datasliceid=5 limit 200;

This would make it a little more random and less likely to return the same rows.
0
 
hnasrCommented:
Compare with this comment using  access database. table a(aid, f1)

1
A query to make a table say random_make_table:

SELECT a.aid, a.f1, Int(Rnd(a.f1)*1000) AS rnd INTO random_make_table
FROM a;

2  
SELECT top 10  *
FROM random_make_table
ORDER BY random_make_table.[rnd];
0
 
lwadwellConnect With a Mentor Commented:
If 5% is more than 200 records ... why not
SELECT * 
FROM   table_a
WHERE  Random() <= 0.05
LIMIT 200; 

Open in new window

Adjust the percentage accordingly ... up or down.
0
 
hnasrCommented:
In MySQl: Table children (cid, cname), select 5% of records.
0
 
onesegunAuthor Commented:
Doh!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Why didn't I think of using Limit!!

Thanks guys....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.