> Not always, but I'd rather get the right answer with difficulty than the wrong one with ease. :)
agreed. I made it a point to mention this so called "feature" in my book. This is a bug they never fixed and they decided to call it a feature. It is, imo, *ridiculous*. Regards, Anthony -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Wednesday, October 12, 2005 6:25 PM To: Greg Stark Cc: Stephan Szabo; Rick Schumeyer; pgsql-sql@postgresql.org Subject: Re: [SQL] pg, mysql comparison with "group by" clause On Wed, 2005-10-12 at 16:54, Greg Stark wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > On Tue, 11 Oct 2005, 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? > > > > In your case, it sounds like the mysql result is wrong. I believe SQL99 > > would allow it if the other columns were functionally dependant upon state > > (as there'd by definition only be one value for the other columns per > > group). > > I believe this is a documented feature. Hehe. When I turn on my windshield wipers and my airbag deploys, is it a documented "feature" if the dealership told me about this behaviour ahead of time? In much the same way, while this behaviour may be documented by MySQL, I can't imagine it really being called a feature. But at least this misbehaviour is documented. However, I think most people in the MySQL universe just stumble onto it by accident when they try it and it works. I'd at least prefer it to throw a warning or notice or something. > MySQL treats "select a,b from t group by a" equivalently to Postgres's > "select distinct on (a) a,b from t" > > I suppose "equivalent" isn't quite true. It's more general since it allows > aggregate functions as well. The equivalently general Postgres syntax is to > have a first() aggregate function and do "select a,first(b) from t group by a". A Subselect would let you do such a thing as well, and while it's more complicated to write, it is likely to be easier to tell just what it's doing. > I'm sure it's very convenient. Not always, but I'd rather get the right answer with difficulty than the wrong one with ease. :) ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
