two of us were in here talking about this very thing yesterday. i wanted to start using enginebeh = 80 and start changing the sql commands as we redo particular sections of our application because as you say set enginebeh=70 is not a permanent solution. but i could not find an example in the help to fully explain the group by. while i agree that this change removes confusion about the returned data, my problem is this, if i select from one of our transaction tables, maybe 30-50 fields do i have to list each of these fields in the group by? what if i link tables? this may seem like a sill q but i only ask as this would make some/all select statements extremly large and if we change the structure of the table we would have to change all the associateed selects as well.
Slán
~M


>Hello Kamil,
>
>I found a reference to this url:http://support.microsoft.com/default.aspx?scid=kb;en-us;813361&Product=Vfp8
>
>In that page it says: "Several changes are made to SQL SELECT statement behavior in VFP8 compared to earlier versions of Visual Fox Pro. The changes are designed to prevent ambiguous data that previous versions of Visual FoxPro (VFP) returned."
>
>I think I have also heard that this change brings VFP's SQL syntax in line with the ANSI SQL standard which (if true) is better for VFP.
>
>With your example...
>SELECT * from test GROUP BY 1,2
>...this is really a bit unclear as to what you expect to have returned. I'm also not sure why you would want to group by in that context without specifying all fields. If you had 2 records, with the same data in pk & nr but something different in field txt and you grouped by just fields 1 & 2, then you would lose that difference.
>
>In this situation you could have the syntax SELECT pk, nr, MAX(txt) GROUP BY 1,2
>
>I think the example...
>SELECT pk, nr, 000 as cn FROM test GROUP BY pk, nr && note no cn column
>...works because VFP views the 000 as cnt as being an aggregate field, or perhaps it recognises that it isn't a field from the original selection.
>
>Lastly, there is the command SET ENGINEBEHAVIOR 70, which makes VFP revert to the old standard. However I don't think that should be a permanent solution.
>
>Stewart