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.
