SQL SELECTING RANDOM ROWS, BUT NOT RESELECTING THEM NEXT TIME

Jayden Bell

Total Post:110

Points:774
Posted by  Jayden Bell
 1010  View(s)
Ratings:
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! 
  1. AVADHESH PATEL

    Post:604

    Points:4228
    Re: SQL selecting random rows, but not reselecting them next time

    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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!