Random selection based on a numeric field

S

Stephen Brown

I have a database of maintenance orders and we want create a report for
follow up customer satisfaction surveys, based on randomly surveying a third
of the orders.

The order number is a numeric field (an integer). The obvious way to select
the orders to be surveyed would seem to be to include in the query only those
order numbers that are divisible by three. How would I achieve this? Or is
there any other way to do it.
 
A

Allen Browne

Try something like this:
SELECT TOP 33 PERCENT Table1.*
FROM Table1
ORDER BY Rnd(Table1.ID);

That's assuming a table named Table1, with a numeric primary key named ID.

The Rnd() function does not actually do anything with the primary key, but
if you don't pass it in the query optimizer won't bother calling the
function for every row.

You need to issue a Randomize to avoid repeating the same sets of values.
 
Top