From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: Relyea, Mike <Mike.Relyea@xerox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Lowest 2 items per
Date: Fri, 1 Jun 2012 17:58:54 +0100
Message-ID: <C8550E3292A64FAAB2662AD03910D484@marktestcr.marktest.pt> (raw)
References: <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
<1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt>
<112ECE6EE7E74DE28DDDB057DB75870C@marktestcr.marktest.pt>
<AF7D9319B29A0242A33C3BF843BD31330EFB8D8D@USA7061MS03.na.xerox.net>
<6C8D39EA43594F12ADF379A91DEECF59@marktestcr.marktest.pt>
<AF7D9319B29A0242A33C3BF843BD31330EFB8DA1@USA7061MS03.na.xerox.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, Mike.Relyea@xerox.com
Subject: Re: Lowest 2 items per
In-Reply-To: <C8550E3292A64FAAB2662AD03910D484@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox