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

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Mario Dankoor
> Sent: Friday, June 01, 2012 2:31 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
> Mike,
>
> try following query it's a variation on a top  N ( = 3) query SELECT
FRS.* FROM
> (
>        SELECT  PRN.make
>               ,PRN.model
>               ,CSM.color
>               ,CSM.type
>               ,cost/yield rank
>        FROM  consumable            CSM
>             ,printers              PRN
>             ,printersandconsumable PCM
>        WHERE 1 = 1
>        AND PCM.printerid    = PRN.printerid
>        AND PCM.consumableid = CSM.consumableid
>        group by PRN.make
>               ,PRN.model
>               ,CSM.color
>               ,CSM.type
>        ) FRS
> WHERE 3 > (
>              SELECT COUNT(*)
>              FROM (
>                     SELECT  PRN.make
>                            ,PRN.model
>                            ,CSM.color
>                            ,CSM.type
>                            ,cost/yield rank
>                      FROM  consumable            CSM
>                           ,printers              PRN
>                           ,printersandconsumable PCM
>                      WHERE 1 = 1
>                      AND PCM.printerid    = PRN.printerid
>                      AND PCM.consumableid = CSM.consumableid
>                      group by PRN.make
>                              ,PRN.model
>                              ,CSM.color
>                              ,CSM.type
>                    ) NXT
>              WHERE 1 = 1
>              AND NXT.make = FRS.make
>              AND NXT.model= FRS.model
>              AND NXT.color= FRS.color
>              AND NXT.type = FRS.type
>              AND NXT.cost <= FRS.cost
>            )

Mario,

This works quite nicely!  I had to add a few criteria to it and the
results it gives does have some ties that I need to figure out how to
break - but that'll be easy because if there is a tie then I don't care
which one wins.  Here's the working query that I am going to modify a
little bit more.

SELECT FRS.* FROM (      SELECT  PRN.Make             ,PRN.Model             ,CSM.Color             ,CSM.Type
 ,CSM.PartNumber             ,Cost/Yield as rank      FROM  Consumables            CSM           ,Printers
PRN          ,PrintersAndConsumables PCM      WHERE 1 = 1      AND PCM.PrinterID    = PRN.PrinterID      AND
PCM.ConsumableID= CSM.ConsumableID      group by PRN.Make             ,PRN.Model             ,CSM.Color
,CSM.Type            ,CSM.Cost             ,CSM.Yield             ,CSM.PartNumber      ) FRS 
WHERE 3 > (            SELECT COUNT(*)            FROM (                   SELECT  PRN.Make
,PRN.Model                         ,CSM.Color                          ,CSM.Type                          ,Cost/Yield
asrank                    FROM  Consumables            CSM                         ,Printers              PRN
             ,PrintersAndConsumables PCM                    WHERE 1 = 1                    AND PCM.PrinterID    =
PRN.PrinterID                   AND PCM.ConsumableID = CSM.ConsumableID                    group by PRN.Make
               ,PRN.Model                            ,CSM.Color                            ,CSM.Type
       ,CSM.Cost                            ,CSM.Yield                  ) NXT            WHERE 1 = 1            AND
NXT.Make= FRS.Make            AND NXT.Model= FRS.Model            AND NXT.Color= FRS.Color            AND NXT.Type =
FRS.Type           AND NXT.rank <= FRS.rank          ) AND 
rank IS NOT NULL
ORDER BY Make, Model, Color, Type;

Thanks for the help!

Mike