Re: Lowest 2 items per - Mailing list pgsql-sql

> -----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