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