X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [64.117.224.130])
	by svr1.postgresql.org (Postfix) with ESMTP id C23E0D1BA96
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  6 Sep 2003 15:35:49 +0000 (GMT)
Received: from svr1.postgresql.org ([64.117.224.193])
	by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new,
	port 10024) with ESMTP id 86910-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat,  6 Sep 2003 12:35:39 -0300 (ADT)
Received: from wolff.to (wolff.to [66.93.249.74])
	by svr1.postgresql.org (Postfix) with SMTP id C3A4CD1BAA2
	for <pgsql-sql@postgresql.org>; Sat,  6 Sep 2003 12:35:33 -0300 (ADT)
Received: (qmail 24612 invoked by uid 500); 6 Sep 2003 15:41:49 -0000
Date: Sat, 6 Sep 2003 10:41:49 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Alexei Chetroi <lexoid@lexa.uniflux-line.net>
Cc: Pgsql-Sql <pgsql-sql@postgresql.org>
Subject: Re: SQL subqueries newbie help
Message-ID: <20030906154149.GA24418@wolff.to>
Mail-Followup-To: Alexei Chetroi <lexoid@lexa.uniflux-line.net>,
	Pgsql-Sql <pgsql-sql@postgresql.org>
References: <20030906110709.GA13384@uniflux-line.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20030906110709.GA13384@uniflux-line.net>
User-Agent: Mutt/1.5.4i
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200309/38
X-Sequence-Number: 15016

On Sat, Sep 06, 2003 at 14:07:09 +0300,
  Alexei Chetroi <lexoid@lexa.uniflux-line.net> wrote:
>  Hi All,
> 
>  I need a little help regarding writing some subqueries. For example I
> have a table "items" which contains columns: itemid, description; and
> another table "events" with columns: itemid, date, event. events.itemid
> references items.itemid. Table events contains events regarding some
> itemid's from table items, so there could be multiple events regarding
> one item from items table. for example:
> 
> Table: items
> itemid    description
> 1         Lamp
> 2         Desk
> 3         HiFi
> 
> Table: events
> itemid    event        date
> 1         purchase     2003-01-01
> 1         repair       2003-01-03
> 1         repair       2003-02-05
> 2         purchase     2003-02-01
> 3         HiFi         2003-02-01
> 
>  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?

You can use group by to do this. If there were a large amount of events per
item, you might get better performance by using disctinct on and limit
in subqueries to get the max and min. That doesn't seem likely for this
problem. Distinct on would also be useful if you want to get the event
with the first and last dates.

The query you want looks something like this:
select description, min(date), max(date) from items, events
  where items.itemid = events.itemid group by description;