I found that article you mention a little confusing because it insists on joining tables in the WHERE clause for backward compatibility. I'm no longer used to that. I also got lost in its description of the different cursors, which don't look like they are all described fully.

I've begun reading the Hacker's Guide to VFP6. It says a couple of relevant things:

(page 21) "...you don't need record numbers. FoxPro has plenty of other tools to find a particular record." There, they are talking about xBase, not SQL, or else they might not have said it.

(page 33) "Some things are easier to do in Xbase while others are easier in SQL. (And some work best with a marriage of the two.) If you're using one approach and your code starts to seem convoluted, step back and see if you can use the other. For example, if a process takes five successive complex queries, stop and think about whether you can do it by setting relations instead.

The second reason we can't arbitrarily recommend one approach over the other is speed: some things are faster in Xbase; others are faster in SQL... you should probably test both solutions to see which runs faster." I have not done so. I lean toward pure SQL because if it works at all, it usually works perfectly.

(page 35) "When you need 'the first 10' or whatever in each group of records, you have to use a hybrid approach that combines SQL with Xbase's record number to produce the desired results. It requires a multi-step process... (Tamar published the solution to this one in the March '94 issue of FoxPro Advisor.)"

Perhaps they found that our "pure SQL" solution is slower because of the non-equi-join. Since it uses RECNO(), one could argue that it isn't quite pure SQL. The RECNO() function is unavailable in Access 97, where I have had to do my more serious SQL. In Access I do a non-equi-join with SUM(1) to get the records numbered for other non-equi-joins.

>What is required is called a self join. See the following
>web site that explains the issue you are encountering in
>great detail:
>
>http://www.networksplus.net/cschreiner/OutFoxed11.html
>
>
>
>>I like your sql solution better than my do while loop.
>>
>>1. It seems more oopsie.
>>2. I vowed to use sql whenever possible
>>3. I learned a new trick
>>
>>Thanks