From: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
To: Rod Taylor <rbt@rbt.ca>
Cc: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: SQL subqueries newbie help
Date: Sun, 7 Sep 2003 14:42:31 +0300
Message-ID: <20030907114226.GA22231@uniflux-line.net> (raw)
In-Reply-To: <1062868895.54737.3.camel@jester>
References: <20030906110709.GA13384@uniflux-line.net>
<1062868895.54737.3.camel@jester>
On Sat, Sep 06, 2003 at 01:21:36PM -0400, Rod Taylor wrote:
> > 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)
[skip]
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?
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: lexoid@lexa.uniflux-line.net, rbt@rbt.ca
Subject: Re: SQL subqueries newbie help
In-Reply-To: <20030907114226.GA22231@uniflux-line.net>
* 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