From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | Alexei Chetroi <lexoid(at)lexa(dot)uniflux-line(dot)net> |
Cc: | Pgsql-Sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: SQL subqueries newbie help |
Date: | 2003-09-06 17:21:36 |
Message-ID: | 1062868895.54737.3.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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)
JOIN (SELECT max(date) AS max_date
, event AS max_event
, item
FROM events
GROUP BY item) AS mx USING (item);
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Stosberg | 2003-09-07 00:38:43 | Re: recursive sql (using the sort_key method) |
Previous Message | Bruno Wolff III | 2003-09-06 15:41:49 | Re: SQL subqueries newbie help |