From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Relyea, Mike <Mike.Relyea@xerox.com>
To: pgsql-sql@postgresql.org
Subject: Re: Lowest 2 items per
Date: Fri, 1 Jun 2012 16:20:32 +0100
Message-ID: <112ECE6EE7E74DE28DDDB057DB75870C@marktestcr.marktest.pt> (raw)
References: <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
<1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt>
Sorry, Mike, previous query was flawed.
This is (hopefully) the correct version
Best,
Oliver
SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
(
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
) subquery1
JOIN
(
SELECT printers.make, printers.model, consumables.color,
consumables.type,cost,yield
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
) subquery2
ON (subquery1.make = subquery2.make
AND subquery1.model = subquery2.model
AND subquery1.color = subquery2.color
AND subquery1.type = subquery2.type)
WHERE subquery2.cost / subquery2.yield <> subquery1.cpp
GROUP BY subquery2.make,subquery2. model,
subquery2.color,subquery2.type,subquery1.cpp
ORDER BY make, model;
----- Original Message -----
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: "Relyea, Mike" <Mike.Relyea@xerox.com>; <pgsql-sql@postgresql.org>
Sent: Friday, June 01, 2012 3:56 PM
Subject: Re: [SQL] Lowest 2 items per
> Hi, Mike,
>
> Can you tell me if this gives what you want, and if it doesn't, what is
> the error reported, or wrong result ?
>
> This is untested query, so Im not sure about it.
>
> Best,
> Oliver
>
> SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
> (
> 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
> ) subquery1
> NATURAL JOIN
> (
> SELECT printers.make, printers.model, consumables.color,
> consumables.type
> 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
> ) subquery2
> WHERE subquery2.cost / subquery2.yield <> subquery1.cpp
> GROUP BY make, model, color,type
> ORDER BY make, model;
>
>
> ----- Original Message -----
> From: "Relyea, Mike" <Mike.Relyea@xerox.com>
> To: <pgsql-sql@postgresql.org>
> Sent: Friday, June 01, 2012 3:34 PM
> Subject: [SQL] Lowest 2 items per
>
>
> 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: oliveiros.cristina@marktest.pt, Mike.Relyea@xerox.com
Subject: Re: Lowest 2 items per
In-Reply-To: <112ECE6EE7E74DE28DDDB057DB75870C@marktestcr.marktest.pt>
* 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