DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=DKIM-Signature:X-Yahoo-Newman-Property:X-YMail-OSG:X-Yahoo-SMTP:Received:References:In-Reply-To:Mime-Version:Content-Transfer-Encoding:Content-Type:Message-Id:Cc:X-Mailer:From:Subject:Date:To;
	b=T8Oi9emqNAh6nPyGlSlkXbI6mlkpeyzcOy/oh6/2CRIkmYZiYLaGbO0wiq3zhIvIXwl3KAAbcHqB6kwwXajMJo9s9rxyaNmrOqtzKtlOg7FgFeU8yWgUc/8/RVCROFjB8ceFgX48ZlqMz3F/gq2Ut4+JPov91kd7oCHIQhOe9Ac=
	;
References: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
In-Reply-To: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
Mime-Version: 1.0 (1.0)
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii
Message-Id: <58DBEBA8-661B-4DB0-9E4C-734A65CBA9A3@yahoo.com>
Cc: "<pgsql-sql@postgresql.org>" <pgsql-sql@postgresql.org>
From: David Johnston <polobo@yahoo.com>
Subject: Re: Lowest 2 items per
Date: Fri, 1 Jun 2012 11:13:27 -0400
To: "Relyea, Mike" <Mike.Relyea@xerox.com>

On Jun 1, 2012, at 10:34, "Relyea, Mike" <Mike.Relyea@xerox.com> wrote:

> I need a little help putting together a query.  I have the tables listed
> below and I need to return the lowest two consumables (ranked by cost
> divided by yield) per printer, per color of consumable, per type of
> consumable.
>=20
> CREATE TABLE printers
> (
>  printerid serial NOT NULL,
>  make text NOT NULL,
>  model text NOT NULL,
>  CONSTRAINT printers_pkey PRIMARY KEY (make , model ),
>  CONSTRAINT printers_printerid_key UNIQUE (printerid ),
> )
>=20
> CREATE TABLE consumables
> (
>  consumableid serial NOT NULL,
>  brand text NOT NULL,
>  partnumber text NOT NULL,
>  color text NOT NULL,
>  type text NOT NULL,
>  yield integer,
>  cost double precision,
>  CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ),
>  CONSTRAINT consumables_consumableid_key UNIQUE (consumableid )
> )
>=20
> CREATE TABLE printersandconsumables
> (
>  printerid integer NOT NULL,
>  consumableid integer NOT NULL,
>  CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid ,
> consumableid ),
>  CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY
> (consumableid)
>      REFERENCES consumables (consumableid) MATCH SIMPLE
>      ON UPDATE CASCADE ON DELETE CASCADE,
>  CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY
> (printerid)
>      REFERENCES printers (printerid) MATCH SIMPLE
>      ON UPDATE CASCADE ON DELETE CASCADE
> )
>=20
> I've pulled together this query which gives me the lowest consumable per
> printer per color per type, but I need the lowest two not just the first
> lowest.
>=20
> SELECT printers.make, printers.model, consumables.color,
> consumables.type, min(cost/yield) AS cpp
> FROM printers
> JOIN printersandconsumables ON printers.printerid =3D
> printersandconsumables.printerid
> JOIN consumables ON consumables.consumableid =3D
> printersandconsumables.consumableid=20
> WHERE consumables.cost Is Not Null=20
> AND consumables.yield Is Not Null
> GROUP BY printers.make, printers.model, consumables.color,
> consumables.type
> ORDER BY make, model;
>=20
>=20
> After doing a google search I didn't come up with anything that I was
> able to use so I'm asking you fine folks!
>=20
> Mike
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql


I would recommend using the "RANK" window function with an appropriate parti=
tion clause in a sub-query then in the outer query you simply WHERE rank <=3D=
 2

You will need to decide how to deal with ties.

David J.=