Message-ID: <4FC90A7F.9020605@gmail.com>
Date: Fri, 01 Jun 2012 20:31:27 +0200
From: Mario Dankoor <m.p.dankoor@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
	rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
To: "Relyea, Mike" <Mike.Relyea@xerox.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Lowest 2 items per
References: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
	<58DBEBA8-661B-4DB0-9E4C-734A65CBA9A3@yahoo.com>
	<AF7D9319B29A0242A33C3BF843BD31330EFB8D61@USA7061MS03.na.xerox.net>
In-Reply-To: 
 <AF7D9319B29A0242A33C3BF843BD31330EFB8D61@USA7061MS03.na.xerox.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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