Date: Sun, 7 Sep 2003 14:42:31 +0300
From: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
To: Rod Taylor <rbt@rbt.ca>
Cc: Alexei Chetroi <lexoid@lexa.uniflux-line.net>,
	Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: SQL subqueries newbie help
Message-ID: <20030907114226.GA22231@uniflux-line.net>
References: <20030906110709.GA13384@uniflux-line.net>
	<1062868895.54737.3.camel@jester>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1062868895.54737.3.camel@jester>
User-Agent: Mutt/1.3.28i

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?