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

On Sun, Sep 07, 2003 at 09:09:35AM -0400, Rod Taylor wrote:
 
 > [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?
> 
> 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
 I found it later. It's called vector aggregates

> 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.

  I can use timestamp instead of date. Generally I see there more than
one way to do it. I'm thinking of writing for example to functions which
returns one row for the very first/last event based on item id.
Currently I've achieved what I need using temporary tables, but I don't
like it much. It seems to bee to ugle. Here what I did:

SELECT
    date,
    event,
    item
    INTO TEMPORARY TABLE firstevent
    FROM events
    WHERE date IN (SELECT min(date) FROM events GROUP BY item);

SELECT
    date,
    event,
    item
    INTO TEMPORARY TABLE lastevent
    FROM events
    WHERE date IN (SELECT max(date) FROM events GROUP BY item);

SELECT
    item,
    f.date,
    f.event,
    l.date,
    l.event
    FROM items AS i
    JOIN firstrot AS f USING(item)
    JOIN lastrot AS l USING(item);

  I know I may use subselects instead of temporary tables in last query,
but I don't know what performance impact I may face. 

  Or should I add additional fields to items table and write a trigger
procedure on events which updates these fields, or keep a two separate
tables for the first/last event of each item and a trigger to update
this tables on events change.
  I'm just learning and doubting about correct approach. Currently I'm 
considering writing a procedures which return row of the first/last 
event using item as key.