Mike,
Now we have something entirely different than the original. We have a table where article, description and location are different because they come from different rows in the articles table, but we have the same number in InStock for each row in the result set. Because the GROUP BY clause was not applied using the description and location, it only represents the total number of articles globally, not the number associated with the specified description and location.

The result set would be valid but very misleading, IMO.

>Hi Walter and George!
>
>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.
>
>George: Just because the code runs, doesn't mean it ain't broke! Queries like these may be returning incorrect results that won't show up until someone audits the results. Setting SQLEngineBehavior lets people avoid learning how to deal with this problem. Backward-compatible = compatible with backward ;), right?
>
>So, how do we correct such queries (assuming they're all *potentially* wrong) without obscuring things or affecting performance and keeping it compatible with SQL Server.
>
>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 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).
>
>In VFP, Part One does the summing and grouping and Part Two joins the results of Part One with the article table.
>
>

SELECT ;
>    Article, ;
>    SUM(InStock) as InStock;
>  FROM Stock ;
>  GROUP BY ;
>    Article ;
>  INTO CURSOR ;
>    DT_StockSummary

>
>

SELECT ;
>    Articles.Article, ;
>    Description, ;
>    Location, ;
>    DT_StockSummary.InStock ;
>  FROM Articles ;
>  INNER JOIN ;
>    DT_StockSummary ON ;
>    Articles.Article = DT_StockSummary.Article

>
>Now there's absolutely no question about the accuracy of the location field (and no need to use Max()). Further *THIS* makes the query *READABLE* (not how many spaces and semi-colons <g>). You might also get to re-use the DT_StockSummary query elsewhere in your system!
>
>It's my understanding SQL Server would have to use a temporary table for DT_StockSummary to leave them broken into two parts. SQL Server 2000 supports table variables which (I hear) is a better solution than temporary tables.