X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 90B4DDAEF2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Oct 2005 15:38:22 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 30823-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 26 Oct 2005 18:38:19 +0000 (GMT)
Received: from wolff.to (wolff.to [66.93.197.194])
	by svr1.postgresql.org (Postfix) with SMTP id 62493DAECC
	for <pgsql-sql@postgresql.org>; Wed, 26 Oct 2005 15:38:19 -0300 (ADT)
Received: (qmail 27241 invoked by uid 500); 26 Oct 2005 18:40:02 -0000
Date: Wed, 26 Oct 2005 13:40:02 -0500
From: Bruno Wolff III <bruno@wolff.to>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: select best price
Message-ID: <20051026184002.GI11447@wolff.to>
Mail-Followup-To: Bruno Wolff III <bruno@wolff.to>,
	Gary Stainburn <gary.stainburn@ringways.co.uk>,
	pgsql-sql@postgresql.org
References: <200510261621.39718.gary.stainburn@ringways.co.uk>
	<200510261634.44796.gary.stainburn@ringways.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <200510261634.44796.gary.stainburn@ringways.co.uk>
User-Agent: Mutt/1.4.2.1i
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.007 required=5 tests=[AWL=0.007]
X-Spam-Level: 
X-Archive-Number: 200510/343
X-Sequence-Number: 23196

On Wed, Oct 26, 2005 at 16:38:48 +0100,
  Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
> On Wednesday 26 October 2005 4:21 pm, Gary Stainburn wrote:
> > Hi folks
> >
> > I've got a table holding item code(cs_id), supplier a/c (co_id) , and
> > price (cs_price).
> >
> > How can I select the rows containing the lowest price for each item
> > code?
> >
> > I've tried various forms of min() etc and know it must be simple but
> > I'm stumped.
> >
> > Gary
> 
> I've come up with the select below.  Is there a better/more efficient 
> way of doing this?
> 
> select cp.cs_id,  from cons_price_details cp, 
>   (select cs_id, min(cs_price) as cs_price 
>         from cons_price_details 
>         group by cs_id
>    ) v 
>    where cp.cs_id = v.cs_id and cp.cs_price = v.cs_price;

If you only need one lowest price entry where there are ties, you could also
use DISTINCT ON (a nonstandard Postgres extension) and ORDER BY that may
execute faster.