On Tue, 2005-10-11 at 16:12, Rick Schumeyer wrote:
> I'm not sure what I was thinking, but I tried the following query in pg:
>
> SELECT * FROM t GROUP BY state;
>
> pg returns an error.
>
> Mysql, OTOH, returns the first row for each state. (The first row with
> "AK", the first row with "PA", etc.)
>
> I'm no SQL expert, but it seems to me that the pg behavior is correct, and
> the mysql result is just weird. Am I correct?
Yes, you are correct. The SQL standard is quite clear that in order to
appear in the select list, an entry must either be in the group by or be
operated upon by an aggregate function. PostgreSQL supports this same
action by way of the non-standard
select distinct on(<fieldlist>), <fieldlist> from ....
Since you don't know for sure which answer you'll get each time, it's
better to KNOW you're doing something that may not be reproduceable than
to accidentally do it when your database SHOULD be throwing an error.
That's just one of many many things MySQL does that makes my head hurt.
For more, search google for "mysql gotchas"