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,
yieldinteger, cost double precision, CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ), CONSTRAINT
consumables_consumableid_keyUNIQUE (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,
CONSTRAINTprintersandconsumables_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