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 )
