From: msi77 <msi77@yandex.ru>
To: "Relyea, Mike" <mike.relyea@xerox.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
References: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
Subject: Re: Lowest 2 items per
MIME-Version: 1.0
Message-Id: <299741338645418@web11h.yandex.ru>
Date: Sat, 02 Jun 2012 17:56:58 +0400
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: quoted-printable

A few of approaches to solve this problem:

http://sql-ex.com/help/select16.php

01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>:
> I need a little help putting together a query. =9AI have the tables lis=
ted
> 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.
>
> CREATE TABLE printers
> (
> =9A=9Aprinterid serial NOT NULL,
> =9A=9Amake text NOT NULL,
> =9A=9Amodel text NOT NULL,
> =9A=9ACONSTRAINT printers_pkey PRIMARY KEY (make , model ),
> =9A=9ACONSTRAINT printers_printerid_key UNIQUE (printerid ),
> )
>
> CREATE TABLE consumables
> (
> =9A=9Aconsumableid serial NOT NULL,
> =9A=9Abrand text NOT NULL,
> =9A=9Apartnumber text NOT NULL,
> =9A=9Acolor text NOT NULL,
> =9A=9Atype text NOT NULL,
> =9A=9Ayield integer,
> =9A=9Acost double precision,
> =9A=9ACONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ),
> =9A=9ACONSTRAINT consumables_consumableid_key UNIQUE (consumableid )
> )
>
> CREATE TABLE printersandconsumables
> (
> =9A=9Aprinterid integer NOT NULL,
> =9A=9Aconsumableid integer NOT NULL,
> =9A=9ACONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid ,
> consumableid ),
> =9A=9ACONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY
> (consumableid)
> =9A=9A=9A=9A=9A=9AREFERENCES consumables (consumableid) MATCH SIMPLE
> =9A=9A=9A=9A=9A=9AON UPDATE CASCADE ON DELETE CASCADE,
> =9A=9ACONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY
> (printerid)
> =9A=9A=9A=9A=9A=9AREFERENCES printers (printerid) MATCH SIMPLE
> =9A=9A=9A=9A=9A=9AON UPDATE CASCADE ON DELETE CASCADE
> )
>
> I've pulled together this query which gives me the lowest consumable pe=
r
> printer per color per type, but I need the lowest two not just the firs=
t
> lowest.
>
> 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
> WHERE consumables.cost Is Not Null
> AND consumables.yield Is Not Null
> GROUP BY printers.make, printers.model, consumables.color,
> consumables.type
> ORDER BY make, model;
>
> 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!
>
> Mike
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql