Re: Lowest 2 items per

On 2012-06-01 5:44 PM, Relyea, Mike wrote:
>> -----Original Message-----
>> From: David Johnston [mailto:polobo@yahoo.com]
>> Sent: Friday, June 01, 2012 11:13 AM
>> To: Relyea, Mike
>> Cc:<pgsql-sql@postgresql.org>
>> Subject: Re: [SQL] Lowest 2 items per
>>
>>
>> I would recommend using the "RANK" window function with an appropriate
>> partition clause in a sub-query then in the outer query you simply
> WHERE
>> rank<= 2
>>
>> You will need to decide how to deal with ties.
>>
>> David J.
>
>
> David,
>
> I've never used window functions before and rank looks like it'd do the
> job quite nicely.  Unfortunately I'm using 8.3 - which I should have
> mentioned in my original request but didn't.  Window functions weren't
> introduced until 8.4 from what I can tell.
>
> Mike
>
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/yieldrank      FROM  consumable            CSM           ,printers              PRN
,printersandconsumablePCM      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          )