Re: Lowest 2 items per

From: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>
To: Oliveiros d'Azevedo Cristina <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Lowest 2 items per
Date: 2012-06-01 20:04:43
Message-ID: AF7D9319B29A0242A33C3BF843BD31330F018FA7@USA7061MS03.na.xerox.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros(dot)cristina(at)marktest(dot)pt]
> Sent: Friday, June 01, 2012 12:59 PM
> To: Relyea, Mike
> Cc: pgsql-sql(at)postgresql(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message msi77 2012-06-02 13:56:58 Re: Lowest 2 items per
Previous Message Relyea, Mike 2012-06-01 19:55:59 Re: Lowest 2 items per