From: msi77 <msi77@yandex.ru>
To: Oliveiros <oliveiros.cristina@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Lowest 2 items per
Date: Sat, 02 Jun 2012 20:32:31 +0400
Message-ID: <308891338654751@web12d.yandex.ru> (raw)
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>
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 many people in the past had 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. 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: msi77@yandex.ru, oliveiros.cristina@gmail.com
Subject: Re: Lowest 2 items per
In-Reply-To: <308891338654751@web12d.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox