[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Select random records

Posted on 2012-09-10
8
Medium Priority
?
4,774 Views
Last Modified: 2012-09-11
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
0
Comment
Question by:onesegun
8 Comments
 
LVL 11

Expert Comment

by:Guru Ji
ID: 38383776
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
 
LVL 17

Expert Comment

by:ramrom
ID: 38384030
what randomness test must the subset pass?
0
 
LVL 35

Expert Comment

by:johnsone
ID: 38384154
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 31

Expert Comment

by:hnasr
ID: 38385257
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
 
LVL 25

Assisted Solution

by:lwadwell
lwadwell earned 120 total points
ID: 38385269
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
 
LVL 31

Expert Comment

by:hnasr
ID: 38385386
In MySQl: Table children (cid, cname), select 5% of records.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 80 total points
ID: 38386718
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
 

Author Closing Comment

by:onesegun
ID: 38386841
Doh!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Why didn't I think of using Limit!!

Thanks guys....
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question