[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
Re: [SQL] Lowest 2 items per
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" To: 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
On Jun 1, 2012, at 10:34, "Relyea, Mike" wrote: > 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 I would recommend using the "RANK" window function with an appropriate partition clause in a sub-query then in the outer query you simply WHERE rank <= 2 You will need to decide how to deal with ties. David J. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
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; - Original Message - From: "Oliveiros d'Azevedo Cristina" To: "Relyea, Mike" ; Sent: Friday, June 01, 2012 3:56 PM Subject: Re: [SQL] Lowest 2 items per 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" To: 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www
Re: [SQL] Lowest 2 items per
> -Original Message- > From: David Johnston [mailto:pol...@yahoo.com] > Sent: Friday, June 01, 2012 11:13 AM > To: Relyea, Mike > Cc: > Subject: Re: [SQL] Lowest 2 items per > > > I would recommend using the "RANK" window function with an appropriate > partition clause in a sub-query then in the outer query you simply WHERE > rank <= 2 > > You will need to decide how to deal with ties. > > David J. David, I've never used window functions before and rank looks like it'd do the job quite nicely. Unfortunately I'm using 8.3 - which I should have mentioned in my original request but didn't. Window functions weren't introduced until 8.4 from what I can tell. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -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
Re: [SQL] Lowest 2 items per
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; * Hello again, Mike, Thank you for your e-mail. Yes, you are right, now, thinking about the way I built it, the query, indeed, leaves out the corner case of models which have just one consumable. I didn't try ur version of the query. Does itork now with your improvements ? Or were they only gramatical ? Best, Oliver Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 12:28 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > Yes, you are right, now, thinking about the way I built it, the query, indeed, > leaves out the corner case of models which have just one consumable. > > I didn't try ur version of the query. > Does itork now with your improvements ? > Or were they only gramatical ? > > Best, > Oliver I only made grammatical changes necessary for the query to function (adding a missing FROM, fully qualifying "SELECT Make" as " SELECT subquery2.Make", etc.) I tried changing the join type to right and left but that did not have the desired result. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
I only made grammatical changes necessary for the query to function (adding a missing FROM, fully qualifying "SELECT Make" as " SELECT subquery2.Make", etc.) I tried changing the join type to right and left but that did not have the desired result. * I see... If we add a query with a union that selects only the single ink printers. Something like 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 UNION SELECT Printers.Make, Printers.Model, Consumables.Color, Consumables.Type, min(Cost/Yield) AS cpp,min(Cost/Yield) AS cpp2 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 HAVING COUNT(*)=1 ORDER BY Make, Model; Can this be the results we're after ? Best, Oliver Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
On 2012-06-01 5:44 PM, Relyea, Mike wrote: -Original Message- From: David Johnston [mailto:pol...@yahoo.com] Sent: Friday, June 01, 2012 11:13 AM To: Relyea, Mike Cc: Subject: Re: [SQL] Lowest 2 items per I would recommend using the "RANK" window function with an appropriate partition clause in a sub-query then in the outer query you simply WHERE rank<= 2 You will need to decide how to deal with ties. David J. David, I've never used window functions before and rank looks like it'd do the job quite nicely. Unfortunately I'm using 8.3 - which I should have mentioned in my original request but didn't. Window functions weren't introduced until 8.4 from what I can tell. Mike Mike, try following query it's a variation on a top N ( = 3) query SELECT FRS.* FROM ( SELECT PRN.make ,PRN.model ,CSM.color ,CSM.type ,cost/yield rank FROM consumableCSM ,printers PRN ,printersandconsumable PCM WHERE 1 = 1 AND PCM.printerid= PRN.printerid AND PCM.consumableid = CSM.consumableid group by PRN.make ,PRN.model ,CSM.color ,CSM.type ) FRS WHERE 3 > ( SELECT COUNT(*) FROM ( SELECT PRN.make ,PRN.model ,CSM.color ,CSM.type ,cost/yield rank FROM consumableCSM ,printers PRN ,printersandconsumable PCM WHERE 1 = 1 AND PCM.printerid= PRN.printerid AND PCM.consumableid = CSM.consumableid group by PRN.make ,PRN.model ,CSM.color ,CSM.type ) NXT WHERE 1 = 1 AND NXT.make = FRS.make AND NXT.model= FRS.model AND NXT.color= FRS.color AND NXT.type = FRS.type AND NXT.cost <= FRS.cost ) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql- > ow...@postgresql.org] On Behalf Of Mario Dankoor > Sent: Friday, June 01, 2012 2:31 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > Mike, > > try following query it's a variation on a top N ( = 3) query SELECT FRS.* FROM > ( >SELECT PRN.make > ,PRN.model > ,CSM.color > ,CSM.type > ,cost/yield rank >FROM consumableCSM > ,printers PRN > ,printersandconsumable PCM >WHERE 1 = 1 >AND PCM.printerid= PRN.printerid >AND PCM.consumableid = CSM.consumableid >group by PRN.make > ,PRN.model > ,CSM.color > ,CSM.type >) FRS > WHERE 3 > ( > SELECT COUNT(*) > FROM ( > SELECT PRN.make >,PRN.model >,CSM.color >,CSM.type >,cost/yield rank > FROM consumableCSM > ,printers PRN > ,printersandconsumable PCM > WHERE 1 = 1 > AND PCM.printerid= PRN.printerid > AND PCM.consumableid = CSM.consumableid > group by PRN.make > ,PRN.model > ,CSM.color > ,CSM.type >) NXT > WHERE 1 = 1 > AND NXT.make = FRS.make > AND NXT.model= FRS.model > AND NXT.color= FRS.color > AND NXT.type = FRS.type > AND NXT.cost <= FRS.cost >) Mario, This works quite nicely! I had to add a few criteria to it and the results it gives does have some ties that I need to figure out how to break - but that'll be easy because if there is a tie then I don't care which one wins. Here's the working query that I am going to modify a little bit more. SELECT FRS.* FROM ( SELECT PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.PartNumber ,Cost/Yield as rank FROM ConsumablesCSM ,Printers PRN ,PrintersAndConsumables PCM WHERE 1 = 1 AND PCM.PrinterID= PRN.PrinterID AND PCM.ConsumableID = CSM.ConsumableID group by PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.Cost ,CSM.Yield ,CSM.PartNumber ) FRS WHERE 3 > ( SELECT COUNT(*) FROM ( SELECT PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,Cost/Yield as rank FROM ConsumablesCSM ,Printers PRN ,PrintersAndConsumables PCM WHERE 1 = 1 AND PCM.PrinterID= PRN.PrinterID AND PCM.ConsumableID = CSM.ConsumableID group by PRN.Make ,PRN.Model ,CSM.Color ,CSM.Type ,CSM.Cost ,CSM.Yield ) NXT WHERE 1 = 1 AND NXT.Make = FRS.Make AND NXT.Model= FRS.Model AND NXT.Color= FRS.Color AND NXT.Type = FRS.Type AND NXT.rank <= FRS.rank ) AND rank IS NOT NULL ORDER BY Make, Model, Color, Type; Thanks for the help! Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Lowest 2 items per
> -Original Message- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.crist...@marktest.pt] > Sent: Friday, June 01, 2012 12:59 PM > To: Relyea, Mike > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Lowest 2 items per > > * I see... > > If we add a query with a union that selects only the single ink printers. > > Something like > > 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 > UNION > SELECT Printers.Make, Printers.Model, Consumables.Color, > Consumables.Type, min(Cost/Yield) AS cpp,min(Cost/Yield) AS cpp2 > 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 HAVING COUNT(*)=1 ORDER BY Make, Model; > > Can this be the results we're after > ? > > Best, > Oliver > Oliver, Thanks for your help. You gave me a workable query. I made a few minor changes to your idea but I really like the solution offered by Mario. It provides more flexibility and is cleaner. For example, with Mario's I can take the lowest 3 easily instead of just the lowest 2. Mike -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
