From: | msi77 <msi77(at)yandex(dot)ru> |
---|---|
To: | Oliveiros <oliveiros(dot)cristina(at)gmail(dot)com> |
Cc: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Lowest 2 items per |
Date: | 2012-06-02 16:32:31 |
Message-ID: | 308891338654751@web12d.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)cristina(at)gmail(dot)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(at)yandex(dot)ru>
>> A few of approaches to solve this problem:
>>
>> http://sql-ex.com/help/select16.php
>>
>> 01.06.2012, 18:34, "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)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(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
From | Date | Subject | |
---|---|---|---|
Next Message | Jan Eskilsson | 2012-06-04 12:08:29 | How to use hstore |
Previous Message | Oliveiros | 2012-06-02 15:00:20 | Re: Lowest 2 items per |