Author: Rafael Copquin
Posted: 2011-05-31 at 16:35:54
That is precisely what I needed!!!
I had no clue of what the newid() function was good for, but it really
selects one random record from the table, every time it is called.
So I do not need to use the VFP rand() function at all, simply send the
command:
select top 1 * from inventory order by newid()
as you suggested, and call it in a loop that will iterate as many times
as needed to pull the desired number of records, like this
cCmd = 'select top 1 * from inventory order by newid()'
for i = 1 to 20 && just want to select 20 records at random
sqlexec(nHandle,cCmd,'curAudit')
select myauditingcursor
append from dbf('curAudit')
endfor
Very cool, thank you very much
BTW Lou, I do have an autoincrementing column, set as identity, but as
you said, if the record is deleted, I might try to get a record that
does not exist, and then I would be screwed (excuse my Spanish, please)
Rafael Copquin
El 31/05/2011 13:11, Stephen Russell escribió:
> On Tue, May 31, 2011 at 11:04 AM, Rafael Copquin<rcopquin@ciudad.com.ar> wrote:
>> I am developing a routine that randomly selects inventory items from a
>> SQL Server table, for inventory control purposes.
>>
>> The item code is numeric, ie: 2345, 7867, 127435, etc. However, there
>> are less than 2000 records in the table
>>
>> So I can apply the vfp rand function like so
>>
>> RandomNbr = int( rand(-1) * 1987 )
>>
>> to select a specific record, say 469, which could have an id number like
>> 8976 and contain an item code like 789678
>>
>> If I were in VFP I would simply locate for recno() = 469 and get the
>> item code.
>>
>> But, what is the equivalent to the recno() function in TSQL, if there is
>> one? And if there is not, how do I get to the particular record?
> -------------------
>
>
> How many do you need to get?
>
> A For loop with this command:
> SELECT TOP 1 * FROM TheTableIneedToAudit ORDER BY NEWID()
>
> Will give you what you need.
>
>
>
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4DE5512A.1070005@ciudad.com.ar
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.