From: | msi77 <msi77(at)yandex(dot)ru> |
---|---|
To: | "Relyea, Mike" <mike(dot)relyea(at)xerox(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 13:56:58 |
Message-ID: | 299741338645418@web11h.yandex.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Oliveiros | 2012-06-02 15:00:20 | Re: Lowest 2 items per |
Previous Message | Relyea, Mike | 2012-06-01 20:04:43 | Re: Lowest 2 items per |