>>
RAND(-1)
>>SELECT TOP 150 *, (RAND() * 1000000) as rn ;
>> FROM mytable ;
>> ORDER BY rn ;
>> INTO CURSOR crs150
>>
>
>Hi Sergey,
>
>Can you tell me
why that works? I believe it does, of course. But I can't see anything in the VFP help that explains how or why you can stick "{RAND() * 1000000)" as a calculated field into a SQL SELECT statement and have it affect which records are included in the TOP 150.
>
>I realize that the result set will have all the columns from mytable, plus a column called rn that will contain the randomly-generated numbers, and that the TOP expression will cause the numbers in the rn column to be the 150 highest of all the numbers generated.
>
>What I don't get is, assuming there is no numeric column in mytable that could also be affected by TOP, why does this guarantee that the result set will consist of randomly-chosen rows rather than just the first 150 in mytable's natural order? In other words, I'd have expected the query simply to interpret TOP 150 as "the first 150 rows in the table" (because there's no field in mytable that can be interpreted as selected by TOP), to pull those rows out, then stick a random number in the added calculated field sort of ...uh....randomly -- :-) -- and without any relationship to the rows in mytable.
>
>I'd be grateful if you would like to take the time to explain this.
TOP 150 takes the first 150 records, according to the selected order. In this case, because of the
ORDER BY rn, that would be the first 150 records, when sorted by this field - which contains the random number.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)