Article Link.

select top 10 * from myTable order by NewId()

Is a very quick and easy random select.
Change 10 to the number of records you want and myTable to your table name of course.

I too have a requirement to produce x random records from a table with a WHERE clause set. But how exactly does this code work ?

"select top 10 * from myTable order by NewId()"

I found similar code for Access at http://www.mvps.org/access/:

"select top 100 mytable.*from mytable
where Randomizer() = 0
order by rnd(isnull(mytable.question) * 0 + 1)"

where the Randomizer function is:
Function Randomizer () As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize : AlreadyDone = True
Randomizer = 0
End Function

This appear to be a variant of the same thing to work around the lack of NewID() in Access. I'm puzzled as to how either do their stuff. It looks like a way of tapping into (or fooling) the actual SQL parsing/searching mechanism. Is this approach legal/reliable ? Any insights would be very gratefully received...

Bob Osola
bobo@tmcs.co.uk

I've developed a Ranomizing select that will return you any number of records in an *almost* random order. You can see patterns but for the most part they are random.

Randomness and Recordsets...

Send me an email with your comments.

Ryan Trudelle-Schwarz
ryan@mamanze.com