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.

>whow,whow, whow.....
>
>George,
>
>>I'd have to disagree completely. First, you obscure the intended output. Second, you're messing with working code (If it ain't broke, don't fix it). Third, and most importantly, you're introducing completely unnecessary processing overhead with the function calls.
>
>I'd have to agree that in existing code, a SET ENGINEBEHAVIOUR is the most easy solution. I use this very same strategy in existing projects. So yes, I agree that in many cases this is the most easy solution.
>
>HOWEVER, it's not compatible with most other SQL implementations. In my cases I'm faced with an application that has to work with either local or remote data. Having the same SQL syntax is important so that I don't have to write different SQL statements for each situation. Further, the undocumented VFP SQL-SELECT ... GROUP BY behaviour is a problem when upsizing existing applications. IMO, it is wise to use the new behaviour for new projects as the chances are that it has to be upsized some day.
>
>Also, I'm not sure about the overhead of having a MAX() function in the clause. Have you done tests to support this claim. If I look at the problem, the MAX() function is not that different from the selecting the last row in a group. Some minor test do not support your claim. Please give me some figures. If the difference is less than say 5%, performance should not be an issue.
>
>I'm aware that in situation that you specificly want to display the last row in a group, there is no real comfortable alternative to the ENGINEBEHAVIOUR 70. OTOH, if you take to following real world example:
>
>

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

>where result is just the article table with the In stock value from the joined stock table. Because all the other fields in the article table are the same within each group. It does not matter to write a
>
>

SELECT Article, MAX(Description) AS Description, MAX(Location) AS Location, ...., SUM(InStock) ;
>    FROM Articles A ;
>        INNER JOIN Stock S ON A.Article = S.Article ;
>    GROUP BY Article

>
>Which should work in all SQL implementations
>
>Walter,