From: 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 15:56:20 +0100
Message-ID: <1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt> (raw)
References: <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
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
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: <1A73A33A6D424324B55B126B61DC4E2A@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