pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alain TESIO <tesio@easynet.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] group by / having
Date: Sun, 19 Dec 1999 19:41:12 +0100
Message-ID: <006201bf4a54$2e9099c0$de5f72c3@atesio> (raw)
References: <2946.945622495@sss.pgh.pa.us>

> > 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





view thread (4+ messages)

Message-ID: <006201bf4a54$2e9099c0$de5f72c3@atesio>
Permalink:  ../006201bf4a54$2e9099c0$de5f72c3@atesio/
Also on:    postgresql.org/message-id/006201bf4a54$2e9099c0$de5f72c3@atesio

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tesio@easynet.fr, tgl@sss.pgh.pa.us
  Subject: Re: [SQL] group by / having
  In-Reply-To: <006201bf4a54$2e9099c0$de5f72c3@atesio>
  References: <2946.945622495@sss.pgh.pa.us> <006201bf4a54$2e9099c0$de5f72c3@atesio>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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