posted 13 years ago
PostgreSQL also does not accept such "loose" syntax.
I am, in fact, surprised at the way MySQL implemented this. If you don't aggregate over a column which you don't group by, which value do you want/expect? MySQL, it seems, just returns the first value encountered, or a random value in the set perhaps? Either way it's a little misleading. Here is an example:
Table X:
Height | Weight | Shoe_Size
1 | 2 | 3
1 | 3 | 4
1 | 4 | 5
...
select Height, avg(Weight), Shoe_Size from X group by Height; => 1, 3, ??? (is it 3, 4, or 5) and more importantly what should it be? I argue it should just throw an error, like HSQLDB, PostgreSQL and a few others do.