Random Select

[pwright]

Has anyone written sql to select exactly 10% of the records from an existing file. The records must be randomly selected from throughout the file.

 

[chpicker]

Well, it won't get you EXACTLY 10%, but it comes close:

SELECT * FROM myTable WHERE RandTest() INTO CURSOR myResult

FUNCTION RandTest()
    IF RAND()<.9
        RETURN .F.
    ENDIF
ENDFUNC

Ian

 

[csr]

If you don't have to use SQL, you could do something
like this ... and this will give you exactly 10%.

Assuming you have 1000 records in the file from which you are selecting records ...

nPicked = 0
do while nPicked < 100
nRecno = round(1000 * rand(),0) && Pick a random recno
select file_to_pick_from
go nRecno
if empty(picked_field)
replace picked_field with &quot;X&quot;
scatter memvar
select file_to_put_records_into
append blank
gather memvar
nPicked = nPicked + 1
endif
enddo

Just another approach ...
Good Luck
Don
dond@csrinc.com