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: <20030906110709.GA13384@uniflux-line.net>
References: <20030906110709.GA13384@uniflux-line.net>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-IRrPZC3JwCzGC0rVuhlS"
Message-Id: <1062868895.54737.3.camel@jester>
Mime-Version: 1.0
Date: Sat, 06 Sep 2003 13:21:36 -0400

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

>  I'd like to write a query which returns following information regarding
> 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?

Something like the below should do it:

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)
  JOIN (SELECT max(date) AS max_date
             , event AS max_event
             , item
          FROM events
      GROUP BY item) AS mx USING (item);

--=-IRrPZC3JwCzGC0rVuhlS
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/Whef6DETLow6vwwRAtQ4AJwOQyEtCldDVpO+yDTROfeLpCXX9gCfbWXB
2tnjFgqccnRcQ9aOBf2H5OI=
=BugX
-----END PGP SIGNATURE-----

--=-IRrPZC3JwCzGC0rVuhlS--