>If this change not break the old style is fine, but "standar sql" in fox? so what about occurs()? alltrim()? and MyFunction()?, exist a lot of vfp-things that break the "standar". Anyway, is hard to tell that any database vendor really implement the complete sql-92 sta (the only i know is borland with interbase) and anyway is best use the optimal ways for tunning...
>
>But putting the sql-server behavior is not sense...why if we have a list of 50 fields in a table we must put ALL fields in group by? The sql way is group by ALL fields, its ilogical, if we have a tree-data stored like planets/continents/country/citys is not sense group by all if we really need group by ONLY city...ok, put max( can solve somes querys but really is still ilogical (i don't need the max value in a list where all values are equal, so wasted processing here)
The reason for having to use all the selected fields in the 'group by' is that nothing else produces sensible results. If you have 5 items with the same code and 3 other fields with other information, and you group on the code, your result set is a code representing a group, and information from what else? The other fields
probably return the contents of the last record in the group, but that's not guaranteed.
For example:
Code date numsold company
1 2003/05/16 12 xyz
2 2003/05/16 8 xyz
2 2003/06/25 9 abc
1 2003/06/25 15 abc
1 2003/07/01 7 lmn
Ok, so now I do a select code, date, numsold, company group by code
What do I get back? I get back 2 records showing 2 different codes, and some other useless junk.
If I'm forced to group by all the fields in the select, then I may not like what I get back, but at least it is sensible information.
Alan