Lowest 2 items per

From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Lowest 2 items per
Date: 2012-06-01 14:34:30
Message-ID: AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Oliveiros d'Azevedo Cristina 2012-06-01 14:56:20 Re: Lowest 2 items per
Previous Message Peter Eisentraut 2012-05-31 19:52:39 Re: order by different on mac vs linux