Re: SQL subqueries newbie help

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 functionI 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
eventsGROUP BY item);
 

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

SELECT   item,   f.date,   f.event,   l.date,   l.event   FROM items AS i   JOIN firstrot AS f USING(item)   JOIN
lastrotAS 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.