Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-sql by date

Next:From: msi77Date: 2012-06-02 13:56:58
Subject: Re: Lowest 2 items per
Previous:From: Relyea, MikeDate: 2012-06-01 19:55:59
Subject: Re: Lowest 2 items per

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group