Random records from VFP SQL

[x***@yahoo.com]

I have a table that have records that can be grouped by an ID field.
In this table there are 4 groups of 25 records each. So in the ID
field there are 4 unique ID values. The records are randomly
distributed through out the table.

lets say one of the ID values is 224. Of the 25 records in the table
that have 224 as the ID I need to randomly pick 4 records.

Is ther a way to write a VFP query that will return 4 random records?

Thanks for the help.

[Olaf Doschke]

Post by x***@yahoo.com
Is ther a way to write a VFP query that will return 4 random records?

My first suggestion is something like that:

select * from table where id = 224 and rand()<(4/25)

But this will not select exactly 4 records each time,
as it's randomly selecting or not selecting the records
with a chance of 4 in 25.

A better way would be making a random numbering,
order by that and take the first 4 of that:

Select top 4 rand() as randomnumber,*;
from table where id = 224 order by randomnumber

This has a disadvantage of not being optimizable.
First each record has to be extended with a random
number, before the first 4 of those numbers can
be determined. But with only 25 records in each group
that should be fast enough.

You should initialize the random number generator
with once calling =rand(-1), somewhere in your main.prg
to get different results with each run.

Bye, Olaf.