>Hi All,
>
>I'm not very familiar with SQL Syntax. I've been trying to figure out what's wrong with the following statement:
>
>
>SELECT nixies, NixieEng, cnt(*) FROM DBF() ORDER BY nixies GROUP BY nixies
>
>
>The field nixies is a c(30) field and NixieEng is a memo field. FoxPro keeps telling me that the 'GROUP BY clause is missing or invalid'.
The problem is that any field not included in the GROUP BY is ambiguous: from which record of the group should VFP pick a value?
VFP 6 and earlier ignored the problem, and picked some record, almost randomly (actually, the first or the last from the group, I am not sure which - but for practical purposes, this is almost as bad as picking a random value).
VFP 7 (?) and later controls this more strictly. You can go back to the earlier behavior with SET ENGINEBEHAVIOR, but beware the potential ambiguity.
>Also, what other SQL type functions are available other than cnt(*)?
max(), min(), avg(), among others.
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)
