Solved

Random sample of 100 records

Posted on 2014-04-21
7
812 Views
Last Modified: 2014-04-25
How could I get a random sample of 100 records from a very large table?
0
Comment
Question by:hrolsons
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
Not sure about the 'very large table' part, but otherwise...

SELECT TOP 100 * FROM your_table
ORDER BY NEWID()
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Tell you what ... How about telling us the business problem that you're trying to tackle, and maybe we'll be able to come up with a better solution.
0
 
LVL 8

Expert Comment

by:ProjectChampion
Comment Utility
Since 2008 R2, SQL Server has a built in feature for this puprpose, i.e. TABLESAMPLE. For instance:

USE AdventureWorks2008R2 ;
GO
SELECT FirstName, LastName
FROM Person.Person
TABLESAMPLE (10 PERCENT) ;
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:hrolsons
Comment Utility
@Jim Horn - I've hired someone to edit photographs for me and I want them to edit a random sample of my whole collection to see how they do.  I didn't just want to send him 100 of the same track meet.

@ProjectChampion - How do you apply TABLESAMPLE to a fixed number, like 100.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
TABLESAMPLE was introduced with SQL Server 2005 and the syntax is:
TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ] )

So in your case:
TABLESAMPLE (100 ROWS)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Having said that TABLESAMPLE is approximate, so if you want exactly 100 you would be better off with Jim's solution.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
And on second thoughts and after doing some testing with TABLESAMPLE (perhaps I should have done that in the first place) the results are not very random at all (which I believe that is akin to saying that someone is not very pregnant :) )

In fact SQL Server's BOL states:
The sample does not have to be a truly random sample at the level of individual rows.
...
If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE. For example, the following query uses the NEWID function to return approximately one percent of the rows of the Sales.SalesOrderDetail table:
...
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now