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:28:01 +0100
Message-ID: <6C8D39EA43594F12ADF379A91DEECF59@marktestcr.marktest.pt> (raw)
References: <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
<1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt>
<112ECE6EE7E74DE28DDDB057DB75870C@marktestcr.marktest.pt>
<AF7D9319B29A0242A33C3BF843BD31330EFB8D8D@USA7061MS03.na.xerox.net>
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
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: <6C8D39EA43594F12ADF379A91DEECF59@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