Re: [SQL] SQL subqueries newbie help
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? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL subqueries newbie help
On Sun, 2003-09-07 at 07:42, Alexei Chetroi wrote: > 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? 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 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. SELECT item , min_date , (SELECT event FROM events WHERE date = min_date AND item = items.item) , max_date , (SELECT event FROM events WHERE date = max_date AND item = items.item) FROM items JOIN (SELECT min(date) AS min_date , max(date) AS max_date , item FROM events GROUP BY item) AS ev USING (item) signature.asc Description: This is a digitally signed message part
Re: [SQL] 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. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] SQL subqueries newbie help
Rod Taylor <[EMAIL PROTECTED]> writes: > 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 > that runs a min on date, and returns the appropriate event -- which > doesn't exist, but I believe could be created SELECT DISTINCT ON might provide a simpler solution to that requirement. Check out the "weather reports" example in the SELECT reference page. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
