Subject: Re: SQL subqueries newbie help
From: Rod Taylor <rbt@rbt.ca>
To: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
In-Reply-To: <20030907114226.GA22231@uniflux-line.net>
References: <20030906110709.GA13384@uniflux-line.net>
	<1062868895.54737.3.camel@jester>
	<20030907114226.GA22231@uniflux-line.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-nsOFcdUehW/KRYvD6FIk"
Message-Id: <1062940175.54737.22.camel@jester>
Mime-Version: 1.0
Date: Sun, 07 Sep 2003 09:09:35 -0400

--=-nsOFcdUehW/KRYvD6FIk
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Sun, 2003-09-07 at 07:42, Alexei Chetroi wrote:
> On Sat, Sep 06, 2003 at 01:21:36PM -0400, Rod Taylor wrote:
> > >  I'd like to write a query which returns following information regard=
ing
> > > each item: item, date of very first event, very last event.
> > >  Is this possible? I think I can write several SELECT queries and
> > > procces them by an application or possibly write some procedure, but
> > > what is better solution?
> >=20
> > Something like the below should do it:
> >=20
> > SELECT item
> >      , min_date
> >      , min_event
> >      , max_date
> >      , max_event
> >   FROM items
> >   JOIN (SELECT min(date) AS min_date
> >              , event AS min_event
> >              , item
> >           FROM events
> >       GROUP BY item) AS mn USING (item)
> [skip]
>=20
>  Thanks everybody for responses. I'm trying this one, but psql complains
> on queries like "SELECT min(date), event FROM events GROUP BY item" that
> events must be GROUPed or used in an aggregate function. Why this
> happens and why it needs be so?

Oh yeah, sorry.  It needs to be like that because otherwise it is unsure
what value to use for event.  What you really want is a min function
that runs a min on date, and returns the appropriate event -- which
doesn't exist, but I believe could be created (see docs on Creating an
Aggregate function if interested)

In the mean time, find the event based on the dates and item id.  This
does assume that an item may only have one event per date.

SELECT item
     , min_date
     , (SELECT event
          FROM events
         WHERE date =3D min_date
           AND item =3D items.item)
     , max_date
     , (SELECT event
          FROM events
         WHERE date =3D max_date
           AND item =3D items.item)
  FROM items
  JOIN (SELECT min(date) AS min_date
             , max(date) AS max_date
             , item
          FROM events
      GROUP BY item) AS ev USING (item)

--=-nsOFcdUehW/KRYvD6FIk
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (FreeBSD)

iD8DBQA/Wy4O6DETLow6vwwRAq5tAJ9ieO30blwFF8iKjDNzyvjP+KaWdACbBHUX
5fz+D/j2vRI6vlQbZ1Q3WPk=
=efer
-----END PGP SIGNATURE-----

--=-nsOFcdUehW/KRYvD6FIk--