Mike,

>Walter: I think George is right to say the query is getting obscured. I don't think performance is the problem though. We need to ask "Does Max(location) get the last physical record instead of the highest value (zzzz instead of aaaa) regardless of it's physical position in the subset?" I once edited a record to make a datetime field the highest value. I didn't edit the last physical record. Max() gave me the highest date, but the date on the last record was lower. It is also obscure to rely on VFP to get the location from the last physical record.

You're right. Using the example under SET ENGINEBEHAVIOR 70, GROUP BY, does get the last physical record of a certain group. Now, IMO its wrong to rely on this behaviour in the first place, since physical locations might change (when sorting table for exmaple) and its not implemented in other SQL dialects. Its typically a thing that VFP allows us to do, though AFAIK its an undocumented feature.

>Thanks to Sergey and Mike Levy, I've learned about derived tables which makes it possible to do this in one query on SQL Server. That's why you'll see DT_ in the following query examples. Too bad VFP can't do this...

>

SELECT ;
>    Articles.Article, Description, Location, DT_StockSummary.InStock ;
>  FROM Articles ;
>    INNER JOIN ;
>    (SELECT ;
>        Article, SUM(InStock) as InStock ;
>      FROM Stock ;
>      GROUP BY ;
>        Article) ;
>    DT_StockSummary ON ;
>      Articles.Article = DT_StockSummary.Article

I totally agree. This certainly is much better. Indeed too bad its not possible within VFP.

>I strongly believe the best way will be to break queries into two parts. Mike Levy told me SQL Server has an optimizer that will benefit from combining these queries (like I did above), but while building, there's nothing preventing us from breaking it into two parts (even on SQL Server).

Breaking it into two queries might be problem with local, remote views and SPT as you have an extra layer to take care of.

Walter,