Re: Lowest 2 items per
От | Oliveiros d'Azevedo Cristina |
---|---|
Тема | Re: Lowest 2 items per |
Дата | |
Msg-id | C8550E3292A64FAAB2662AD03910D484@marktestcr.marktest.pt обсуждение исходный текст |
Ответ на | Lowest 2 items per ("Relyea, Mike" <Mike.Relyea@xerox.com>) |
Ответы |
Re: Lowest 2 items per
("Relyea, Mike" <Mike.Relyea@xerox.com>)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: