Hi jim,

>>But if you set EB to 70, will this:
>>
>>Select UserID, MemberName, Count(*) as NTally Group by UserID
>>
>>...typically run faster than this:
>>
>>Select UserID, MemberName, Count(*) as NTally Group by UserID, MemberName
>>
>>????
>
>I don't know I never timed it. Setting EB to 70 to me is like saying set SQL to not compliant with the standard and I don't like it. It is a last resort for running old code in VFP9 without making major modifications to the code itself. Writing new code should follow the ANSI SQL Standard and it should not have any problem with the group by rules.

Though that might be true. However VFP8 seriously lacked the SQL compliance to rewrite such SQL queries. VFP9 certainly made things a lot better, but still it seems clumsy to me the way we have to rewrite the issue. for example,

SELECT Cus.*, count(ord_pk) FROM Orders INNER JOIN Customer Cus ON ord_cusfk = cus_pk GROUP BY cus_pk

into something like

SELECT cus.*, cnt FROM Customer cus INNER JOIN (SELECT count(ord_pk) as cnt, ord_cusfk FROM Orders GROUP BY ord_cusfk) AS D ON cus_pk = ord_cusfk

Esspecially when things become a bit more complex than this. Not only is the latter just harder to write (Just ask 100 VFP how to write the equivalent of the first VFP7 SQL into ANSI SQL and be surprised how many actually draw up a valid equivalent), but the executionplan is totally different cuasing different execution performance characteristics.

Since in my project I worked a lot with local views and remote views, mainly written for VFP8 (We are just making the transition to VFP9 because there were a few nasty VFP9 SQL bugs which prevented us from going forward) in which we did not have the luxury to have subqueries written in the way that VFP9 does allow, nor could split it into two SQL statements. I was forced to write different SQL for the Local and remote views.

To be honest, I think the ansi SQL should provide us with some mechanism to do more or less what we did in VFP for years. Not looking too strict on the fields missing an aggregation function, but by default take the last (non null) value as VFP did.