Hi Jon!
> One thing I never have understood w.r.t. GROUP BY is why the SQL
> engine do not find the group by elements. You don't seem to have any
> choice do you? I mean if you do a select:
> SELECT a,b,c,d,MAX(e),COUNT(*),AVERAGE(f) FROM foo WHERE something
> GROUP BY a,b,c,d;
> That is, you _have to_ group by all columns that are not a function
> extracting something from all columns.
I don't think so. I think this is natural, if you don't include a column
in the GROUP BY clause and it appears in the SELECT clause, the SQL engine
can't tell you what value must appear in the row.
Let me explain this, as I don't write english very well and I can get
misunderstood.
Given the following data:
age | name
-----------
30 | John
30 | Peter
And the following SELECT:
SELECT age, name FROM <data> GROUP BY age
What would you get?
Answer A)
age | name
------------
30 | John
Answer B)
age | name
------------
30 | Peter
Please check only one. :-D
I think you get the point. The SQL engine can't give you ambigous
results, as the last select will. You must either A) give a hint as what
result do you want [MAX(LENGTH(name))] or B) include the column in the GROUP
BY or C) don't include it at all.
Please note that we can argue that in cases we know we will get the same
results in all the rows gruped, we can safely include a column in the SELECT
but not in the GROUP BY. Wrong. First, the SQL engine don't know that, and
second, no one can safely guarantee this state will change in the future.
(MySQL doesn't have data integrity checking first).
Hope this helps, and forgive me if I didn't understand your question.
Regards,
Jose Miguel.
