General information
Category:
Databases,Tables, Views, Indexing and SQL syntax
I've been thinking about that, and I think you can do it in one of 2 ways, though one is not that elegant.
First method (preferable):
=RAND(-1)
COPY FIELDS primarykey TO temp FOR RAND()<0.10
SELECT * FROM mytable ;
WHERE primarykey IN ;
(SELECT primarykey FROM temp) ;
INTO CURSOR result
ERASE temp.dbf
Second method (clumsy but interesting):
=RAND(-1)
SELECT * FROM mytable ;
WHERE TenPctSample() ;
INTO CURSOR result
FUNCTION TenPctSample
RETURN RAND()<0.10
The problem with doing a SELECT...WHERE RAND()<0.10 is that VFP evaluates the WHERE clause before it even executes it. Therefore, it would evaluate the WHERE clause up-front to be .T. only 10% of the time and .F. 90% of the time; therefore, the SELECT would return NOTHING 90% of the time. However, VFP won't attempt to evaluate a user-defined function up-front, thus the second method above works. It's not that straightforward, and evaulating a user-defined function can slow things down.
Actually, I just thought of a 3rd way, but I don't like it performance-wise on a huge table:
SELECT *,RAND()<0.10 AS itsok ;
FROM mytable ;
HAVING itsok=.T. ;
INTO CURSOR result
The above will essentially build a copy of the ENTIRE table with an extra field tacked on, and then the HAVING clause will strip out the 90% of the records you don't want. It works, but it entails a lot of overhead if you're working on a huge table.
--Brad
Previous
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only