From: Rod Taylor <rbt@rbt.ca>
To: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: SQL subqueries newbie help
Date: Sat, 06 Sep 2003 13:21:36 -0400
Message-ID: <1062868895.54737.3.camel@jester> (raw)
In-Reply-To: <20030906110709.GA13384@uniflux-line.net>
References: <20030906110709.GA13384@uniflux-line.net>
> 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);
Attachments:
signature.asc (application/pgp-signature, 186B) download
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rbt@rbt.ca, lexoid@lexa.uniflux-line.net
Subject: Re: SQL subqueries newbie help
In-Reply-To: <1062868895.54737.3.camel@jester>
* 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