Hi Jon!
> Actually, I got (A) by testing. ;-) However, should you? The result
> looks like it is arbitrary, and another day, you get (B).
Yes, looks arbitrary but really isn't. You are getting the first value
of the group in no order. Add this to the query and see the result:
... ORDER BY name DESC
(Note: When I say "in no order" I mean the order you get in "SELECT *
FROM foo")
> Anyway, my question (frustration) does not seem to be interesting in
> mysql as you can get such answers. In Oracle, you select statement is
> not possible at all:
Certainly, like it should be.
However, reading the MySQL manual, they explain why MySQL is so relaxed
about the columns listed in the GROUP BY clause. Read the chapter "6.3.7
Functions for use with GROUP BY clauses". There is clearly explained that
the MySQL team choose not to force the existence of all columns in the GROUP
BY for perfomance reasons. They also give an example there.
However, you can force MySQL to require a full GROUP BY by using the
following parameter in the mysqld command line:
--sql-mode=ONLY_FULL_GROUP_BY
Or by forcing ANSI SQL compliance: --ansi
Cheers,
Jose Miguel.
--
Jon Haugsand, Jon-H.Haugsand@stripped
http://www.norges-bank.no
