Lowest 2 items per
От | Relyea, Mike |
---|---|
Тема | Lowest 2 items per |
Дата | |
Msg-id | AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net обсуждение исходный текст |
Ответы |
Re: Lowest 2 items per
Re: Lowest 2 items per |
Список | 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, 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
В списке pgsql-sql по дате отправления: