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

x
Solved

# Select random records

Posted on 2012-09-10
Medium Priority
4,774 Views
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;
``````

Thanks,

OS
0
Question by:onesegun

LVL 11

Expert Comment

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

ID: 38384030
what randomness test must the subset pass?
0

LVL 35

Expert Comment

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

LVL 31

Expert Comment

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

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;
``````
Adjust the percentage accordingly ... up or down.
0

LVL 31

Expert Comment

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

LVL 35

Accepted Solution

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

ID: 38386841
Doh!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Why didn't I think of using Limit!!

Thanks guys....
0

## Featured Post

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…
###### Suggested Courses
Course of the Month17 days, 14 hours left to enroll

#### 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.