> -----Original Message----- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 11:21 AM > To: Oliveiros d'Azevedo Cristina; Relyea, Mike; pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > 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; >
Oliver, I had to make a few grammatical corrections on your query to get it to run, but once I did it gave me almost correct results. It leaves out all of the printer models that only have one consumable with a cost. Some printers might have more than two black inks and some might have only one. Your query only returns those printers that have two or more. Here's your query with the corrections I had to make SELECT subquery2.Make, subquery2.Model, subquery2.Color,subquery2.Type, subquery1.cpp, min(Cost/Yield) as cpp2 FROM( 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; Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
