Home > DeveloperSection > Forums > Select n random rows from SQL Server table
Royce Roy
Royce Roy

Total Post:134

Points:938
Posted on    May-06-2013 2:08 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 683  View(s)
Rate this:
Hi Everyone!

I've got a SQL Server table with about 50,000 rows in it. I want to select about 5,000 of those rows at random. I've thought of a complicated way, creating a temp 

table with a "random number" column, copying my table into that, looping through the temp table and updating each row with RAND(), and then selecting from that table 

where the random number column < 0.1. I'm looking for a simpler way to do it, in a single statement if possible.

This article suggest using the NEWID() function. That looks promising, but I can't see how I could reliably select a certain percentage of rows.

Anybody ever do this before? Any ideas?

Thanks in advance!


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-06-2013 9:03 AM

Hi Royce!

select top 10 percent * from [yourtable] order by newid()
In response to the "pure trash" comment concerning large tables: you could do it like this to improve performance.

select  * from [yourtable] where [yourPk] in 
(select top 10 percent [yourPk] from [yourtable] order by newid())
The cost of this will be the key scan of values plus the join cost, which on a large table with a small percentage selection should be reasonable.


Don't want to miss updates? Please click the below button!

Follow MindStick