Re: SQL subqueries newbie help - Mailing list pgsql-sql

From Rod Taylor
Subject Re: SQL subqueries newbie help
Date
Msg-id 1062868895.54737.3.camel@jester
Whole thread Raw
In response to SQL subqueries newbie help  (Alexei Chetroi <lexoid@lexa.uniflux-line.net>)
Responses Re: SQL subqueries newbie help
List 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); 

pgsql-sql by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: SQL subqueries newbie help
Next
From: Mark Stosberg
Date:
Subject: Re: recursive sql (using the sort_key method)