Re: Lowest 2 items per - Mailing list pgsql-sql

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=learn

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 start from scratch, but I should've realized that
manypeople in the past had the same problem as Mike and I should have googled a little instead of trying to re-invent
thewheel. 
>
> 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.  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.
>>>
>>> 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 ),
>>> )
>>>
>>> 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 )
>>> )
>>>
>>> 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
>>> )
>>>
>>> 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.
>>>
>>> SELECT printers.make, printers.model, consumables.color,
>>> consumables.type, min(cost/yield) AS cpp
>>> FROM printers
>>> JOIN printersandconsumables ON printers.printerid =
>>> printersandconsumables.printerid
>>> JOIN consumables ON consumables.consumableid =
>>> 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