Обсуждение: 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, 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
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" <Mike.Relyea@xerox.com> To: <pgsql-sql@postgresql.org> 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, 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
On Jun 1, 2012, at 10:34, "Relyea, Mike" <Mike.Relyea@xerox.com> 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 outerquery you simply WHERE rank <= 2 You will need to decide how to deal with ties. David J.
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 cppFROM printersJOIN printersandconsumables ON printers.printerid =printersandconsumables.printeridJOINconsumables ON consumables.consumableid =printersandconsumables.consumableidWHERE consumables.costIs Not NullAND consumables.yield Is Not NullGROUP BY printers.make, printers.model, consumables.color,consumables.type)subquery1JOIN(SELECT printers.make, printers.model, consumables.color,consumables.type,cost,yieldFROMprintersJOIN printersandconsumables ON printers.printerid =printersandconsumables.printeridJOINconsumables ON consumables.consumableid =printersandconsumables.consumableidWHERE consumables.costIs Not NullAND 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.cppGROUP BY subquery2.make,subquery2.model, subquery2.color,subquery2.type,subquery1.cppORDER BY make, model; ----- Original Message ----- From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt> To: "Relyea, Mike" <Mike.Relyea@xerox.com>; <pgsql-sql@postgresql.org> 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" <Mike.Relyea@xerox.com> > To: <pgsql-sql@postgresql.org> > 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
> -----Original Message----- > From: David Johnston [mailto:polobo@yahoo.com] > Sent: Friday, June 01, 2012 11:13 AM > To: Relyea, Mike > Cc: <pgsql-sql@postgresql.org> > 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
> -----Original Message----- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type) subquery1JOIN(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.ConsumableIDWHERE Consumables.Cost Is Not NullAND 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.cppORDER BY Make, Model; Mike
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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type) subquery1JOIN(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.ConsumableIDWHERE Consumables.Cost Is Not NullAND 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.cppORDER 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
> -----Original Message----- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@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
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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type) subquery1JOIN(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.ConsumableIDWHERE Consumables.Cost Is Not NullAND 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.ConsumableIDWHERE Consumables.Cost Is Not NullAND Consumables.Yield IsNot NullGROUP BY Printers.Make, Printers.Model, Consumables.Color, Consumables.Type HAVING COUNT(*)=1ORDER 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
On 2012-06-01 5:44 PM, Relyea, Mike wrote:
>> -----Original Message-----
>> From: David Johnston [mailto:polobo@yahoo.com]
>> Sent: Friday, June 01, 2012 11:13 AM
>> To: Relyea, Mike
>> Cc:<pgsql-sql@postgresql.org>
>> 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/yieldrank FROM consumable CSM ,printers PRN
,printersandconsumablePCM 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 consumable CSM ,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 )
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@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 consumable CSM
> ,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 consumable CSM
> ,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 Consumables CSM ,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
asrank FROM Consumables CSM ,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
> -----Original Message----- > From: Oliveiros d'Azevedo Cristina [mailto:oliveiros.cristina@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
A few of approaches to solve this problem: http://sql-ex.com/help/select16.php 01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>: > 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
Nice resource, msi77.<br /><br />Thanx for sharing.<br /><br />I wasn't aware of none of these techniques, actually, so Itried to start from scratch, but I should've realized that many people in the past had the same problem as Mike and I shouldhave googled a little instead of trying to re-invent the wheel.<br /><br />Anyway, this is great information and I'msure it will be useful in the future.<br />Again thanx for sharing.<br /><br />Best,<br />Oliver<br /><br /><br /><br/><div class="gmail_quote">2012/6/2 msi77 <span dir="ltr"><<a href="mailto:msi77@yandex.ru" target="_blank">msi77@yandex.ru</a>></span><br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex">A few of approaches to solve this problem:<br /><br /><a href="http://sql-ex.com/help/select16.php"target="_blank">http://sql-ex.com/help/select16.php</a><br /><br /> 01.06.2012,18:34, "Relyea, Mike" <<a href="mailto:Mike.Relyea@xerox.com">Mike.Relyea@xerox.com</a>>:<br /><div class="HOEnZb"><divclass="h5">> I need a little help putting together a query. I have the tables listed<br /> > belowand I need to return the lowest two consumables (ranked by cost<br /> > divided by yield) per printer, per colorof consumable, per type of<br /> > consumable.<br /> ><br /> > CREATE TABLE printers<br /> > (<br /> > printerid serial NOT NULL,<br /> > make text NOT NULL,<br /> > model text NOT NULL,<br /> > CONSTRAINTprinters_pkey PRIMARY KEY (make , model ),<br /> > CONSTRAINT printers_printerid_key UNIQUE (printerid ),<br/> > )<br /> ><br /> > CREATE TABLE consumables<br /> > (<br /> > consumableid serial NOT NULL,<br/> > brand text NOT NULL,<br /> > partnumber text NOT NULL,<br /> > color text NOT NULL,<br /> > type text NOT NULL,<br /> > yield integer,<br /> > cost double precision,<br /> > CONSTRAINT consumables_pkeyPRIMARY KEY (brand , partnumber ),<br /> > CONSTRAINT consumables_consumableid_key UNIQUE (consumableid)<br /> > )<br /> ><br /> > CREATE TABLE printersandconsumables<br /> > (<br /> > printeridinteger NOT NULL,<br /> > consumableid integer NOT NULL,<br /> > CONSTRAINT printersandconsumables_pkeyPRIMARY KEY (printerid ,<br /> > consumableid ),<br /> > CONSTRAINT printersandconsumables_consumableid_fkeyFOREIGN KEY<br /> > (consumableid)<br /> > REFERENCES consumables (consumableid)MATCH SIMPLE<br /> > ON UPDATE CASCADE ON DELETE CASCADE,<br /> > CONSTRAINT printersandconsumables_printerid_fkeyFOREIGN KEY<br /> > (printerid)<br /> > REFERENCES printers (printerid)MATCH SIMPLE<br /> > ON UPDATE CASCADE ON DELETE CASCADE<br /> > )<br /> ><br /> > I've pulledtogether this query which gives me the lowest consumable per<br /> > printer per color per type, but I need thelowest two not just the first<br /> > lowest.<br /> ><br /> > SELECT printers.make, printers.model, consumables.color,<br/> > consumables.type, min(cost/yield) AS cpp<br /> > FROM printers<br /> > JOIN printersandconsumablesON printers.printerid =<br /> > printersandconsumables.printerid<br /> > JOIN consumables ONconsumables.consumableid =<br /> > printersandconsumables.consumableid<br /> > WHERE consumables.cost Is Not Null<br/> > AND consumables.yield Is Not Null<br /> > GROUP BY printers.make, printers.model, consumables.color,<br/> > consumables.type<br /> > ORDER BY make, model;<br /> ><br /> > After doing a googlesearch I didn't come up with anything that I was<br /> > able to use so I'm asking you fine folks!<br /> ><br/> > Mike<br /> ><br /> > --<br /> > Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> > To make changes to your subscription:<br />> <a href="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /> --<br /> Sent via pgsql-sql mailing list (<ahref="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br /><ahref="http://www.postgresql.org/mailpref/pgsql-sql" target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></div></div></blockquote></div><br />
Thank you for reply, Oliver. I want that you'll pay attention to the learn exercises which can by made under PostgreSQL among few other DBMS: http://sql-ex.ru/exercises/index.php?act=learn 02.06.2012, 19:00, "Oliveiros" <oliveiros.cristina@gmail.com>: > Nice resource, msi77. > > Thanx for sharing. > > I wasn't aware of none of these techniques, actually, so I tried to start from scratch, but I should've realized that manypeople in the past had the same problem as Mike and I should have googled a little instead of trying to re-invent thewheel. > > Anyway, this is great information and I'm sure it will be useful in the future. > Again thanx for sharing. > > Best, > Oliver > > 2012/6/2 msi77 <msi77@yandex.ru> >> A few of approaches to solve this problem: >> >> http://sql-ex.com/help/select16.php >> >> 01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>: >>> 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