pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
group by / having
4+ messages / 2 participants
[nested] [flat]

* group by / having
@ 1999-12-19 14:28 Alain TESIO <tesio@easynet.fr>
  0 siblings, 0 replies; 4+ messages in thread

From: Alain TESIO @ 1999-12-19 14:28 UTC (permalink / raw)
  To: pgsql-sql

Hello,

Do you know why this command doesn't work ?

> select * from T;
x|y
-+-
1|1
1|2
2|1
2|2
2|3

> select X,Y from T group by X having Y=min(Y);

ERROR:  Illegal use of aggregates or non-group column in target list


My goal is quite simple : get only one line per X value (the value which is
returned for Y is not important as long as it's one of the values linked to
the right X).

The query "select X,Y from T group by X" works under MySQL and
returns exactly what I want, how can I do it in PostgreSQL ?

Thanks for your help,
Alain





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* group by / having
@ 1999-12-19 16:04 Alain TESIO <tesio@easynet.fr>
  1999-12-19 16:54 ` Re: [SQL] group by / having Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 4+ messages in thread

From: Alain TESIO @ 1999-12-19 16:04 UTC (permalink / raw)
  To: pgsql-sql

Hello,

Do you know why this command doesn't work ?

> select * from T;
x|y
-+-
1|1
1|2
2|1
2|2
2|3

> select X,Y from T group by X having Y=min(Y);

ERROR:  Illegal use of aggregates or non-group column in target list


My goal is quite simple : get only one line per X value (the value which is
returned for Y is not important as long as it's one of the values linked to
the right X).

The query "select X,Y from T group by X" works under MySQL and
returns exactly what I want, how can I do it in PostgreSQL ?

Thanks for your help,
Alain





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] group by / having
  1999-12-19 16:04 group by / having Alain TESIO <tesio@easynet.fr>
@ 1999-12-19 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-12-19 18:41   ` Re: [SQL] group by / having Alain TESIO <tesio@easynet.fr>
  0 siblings, 1 reply; 4+ messages in thread

From: Tom Lane @ 1999-12-19 16:54 UTC (permalink / raw)
  To: Alain TESIO <tesio@easynet.fr>; +Cc: pgsql-sql

"Alain TESIO" <tesio@easynet.fr> writes:
> Do you know why this command doesn't work ?
> 	select X,Y from T group by X having Y=min(Y);
> ERROR:  Illegal use of aggregates or non-group column in target list

You can't do that because the HAVING condition is evaluated *after*
aggregation of the rows with the same X into a single row.  (As it must
be, in order to refer to the min() result over those input rows.)
So, there's no unique Y to use.  Similarly, you can't refer directly
to Y in the SELECT target list.  However, you can refer to an aggregate
function computed on Y.

> My goal is quite simple : get only one line per X value (the value which is
> returned for Y is not important as long as it's one of the values linked to
> the right X).

The usual solution is simply

	select X,min(Y) from T group by X

Of course max(Y) would work as well given the rules you set.

> The query "select X,Y from T group by X" works under MySQL and
> returns exactly what I want,

Then MySQL is broken.  That query is invalid according to the SQL
standard, because there isn't any well-defined way to choose which Y
value to return.  To get a result that is not implementation-dependent,
you must add some additional constraint on which Y you want --- that's
what writing min() or max() does for you.

BTW, I agree Postgres' error message is not very helpful.  The next
release will say something like
	Attribute T.Y must be GROUPed or used in an aggregate function
If anyone's got suggestions for the best wording, let me know.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] group by / having
  1999-12-19 16:04 group by / having Alain TESIO <tesio@easynet.fr>
  1999-12-19 16:54 ` Re: [SQL] group by / having Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-12-19 18:41   ` Alain TESIO <tesio@easynet.fr>
  0 siblings, 0 replies; 4+ messages in thread

From: Alain TESIO @ 1999-12-19 18:41 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

> > Do you know why this command doesn't work ?
> > select X,Y from T group by X having Y=min(Y);
> > ERROR:  Illegal use of aggregates or non-group column in target list
>
> You can't do that because the HAVING condition is evaluated *after*
> aggregation of the rows with the same X into a single row.  (As it must
> be, in order to refer to the min() result over those input rows.)
> So, there's no unique Y to use.  Similarly, you can't refer directly
> to Y in the SELECT target list.  However, you can refer to an aggregate
> function computed on Y.
>
> > My goal is quite simple : get only one line per X value (the value which
is
> > returned for Y is not important as long as it's one of the values linked
to
> > the right X).
>
> The usual solution is simply
>
> select X,min(Y) from T group by X

This solves my problem but I'm surprised that having doesn't behave as
I expected. Let's take an example :

X Y Z
1 1 A
1 2 B
2 1 C
3 1 D
3 2 E
3 3 F

I want one line for each X value where the Y value is minimal, and I want
to get the T column also.

Isn't this a part of the SQL standard ?

select X,Y,Z from T group by X having Z=min(Z)

Any warkaround in postgresql ?

Regards,
Alain





^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-12-19 18:41 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-12-19 14:28 group by / having Alain TESIO <tesio@easynet.fr>
1999-12-19 16:04 group by / having Alain TESIO <tesio@easynet.fr>
1999-12-19 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-12-19 18:41   ` Alain TESIO <tesio@easynet.fr>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox