From: msi77 <msi77@yandex.ru>
To: Oliveiros <oliveiros.cristina@gmail.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
In-Reply-To: 
 <CAGp7Z5Mn17D=7HKqDrsLYmKn=2RLDKM2gaehm3g-iqoqXqNhvg@mail.gmail.com>
References: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
	<299741338645418@web11h.yandex.ru>
	<CAGp7Z5Mn17D=7HKqDrsLYmKn=2RLDKM2gaehm3g-iqoqXqNhvg@mail.gmail.com>
Subject: Re: Lowest 2 items per
MIME-Version: 1.0
Message-Id: <308891338654751@web12d.yandex.ru>
Date: Sat, 02 Jun 2012 20:32:31 +0400
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: quoted-printable

Thank you for reply, Oliver.
I want that you'll pay attention to the learn exercises which can by made=
 under PostgreSQL among few other DBMS:
http://sql-ex.ru/exercises/index.php?act=3Dlearn=20

02.06.2012, 19:00, "Oliveiros" <oliveiros.cristina@gmail.com>:
> Nice resource, msi77.
>
> Thanx for sharing.
>
> I wasn't aware of none of these techniques, actually, so I tried to sta=
rt from scratch, but I should've realized that many people in the past ha=
d the same problem as Mike and I should have googled a little instead of =
trying to re-invent the wheel.
>
> Anyway, this is great information and I'm sure it will be useful in the=
 future.
> Again thanx for sharing.
>
> Best,
> Oliver
>
> 2012/6/2 msi77 <msi77@yandex.ru>
>> 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 l=
isted
>>> 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 =
per
>>> printer per color per type, but I need the lowest two not just the fi=
rst
>>> 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
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql