>>I need to create a randomly selected 20% sample on a table. Here is the code that I use:
>>
>>SELECT finallist
>>ALTER table finallist ADD COLUMN RandSel n(1,0)
>>
>>FOR i = 1 TO INT(reccount()*.2)
>> LOCATE for RECNO() = INT(RAND()*RECCOUNT())
>> IF finallist.RandSel <> 1 then
>> replace RandSel WITH 1
>> ELSE
>> SKIP 1
>> replace RandSel WITH 1
>> ENDIF
>>ENDFOR
>>
>>SELECT * from finallist WHERE RandSel = 1 INTO TABLE RandomList
>>
>if you don't need to keep track of the random number in the source table, you could do this [23418 is a randomly selected number]
>
>select top 20 percent *, int(Rand()*23418) as RandSel ;
> from finallist ;
> order by RandSel ;
> into table RandomList
>
Without actually testing it, I would say that this will select the same 20% of the table and the output is in a random order!
Update: I guess it depends on whether the engine needs to select everything in order to then determine what 20% of the results is going to be - I'm tending towards this.
censored.