Home > DeveloperSection > Forums > SQL selecting random rows, but not reselecting them next time
Jayden Bell
Jayden Bell

Total Post:105

Points:739
Posted on    May-06-2013 2:04 AM

 MSSQL Server MSSQL Server 
Ratings:


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

I'm making a random selection of 50 items, but when another call is made, I want to select another random 50 that DOES NOT include anything selected previously. How 

best to do this? Is there an SQL statement that handles this?

// Collect 50 random entries
$stmt = $db->query('
SELECT * FROM db
ORDER BY RAND() 
LIMIT 50    
');

Thanks in advance! 


AVADHESH PATEL

Total Post:604

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

Hi Jayden!

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