The self join stuff is pretty useful though. I've used it myself, when the need appeared. I reccomend learning the technique, even if it requires a couple hours of play.

The important thing is that you are selecting from the same table twice.
select * from mytable a, mytable b where a.keyfield1 = b.keyfield1 and cnt(*) = 2

or something like that. Play with it, its a powerful technique!

>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