Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retrieving random selection of records

Other

Retrieving random selection of records

by  AWithers  Posted    (Edited  )
This FAQ outlines a way of retrieving a random set of records for audit or other prurposes.

The method requires that the table has a numerical field on which to operate

the sql is very simple

select top X from yourtable
order by rnd(numericalfield)

This will give the x records and will be different everytime it is run.

If you need to ensure that you don't get any of the records from last time it was run then the keyfield(s) of the records could additionally be placed in a temporary table (e.g. tblauditedlasttime)and excluded from the search

e.g.

select top X from yourtable
order by rnd(numericalfield)
where keyfield not in (select keyfield from tblauditedlasttime)

I hope this is useful to someone

Andy
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top