From: Mario Dankoor <m.p.dankoor@gmail.com>
To: Relyea, Mike <Mike.Relyea@xerox.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Lowest 2 items per
Date: Fri, 01 Jun 2012 20:31:27 +0200
Message-ID: <4FC90A7F.9020605@gmail.com> (raw)
In-Reply-To: <AF7D9319B29A0242A33C3BF843BD31330EFB8D61@USA7061MS03.na.xerox.net>
References: <AF7D9319B29A0242A33C3BF843BD31330EFB8CCE@USA7061MS03.na.xerox.net>
<58DBEBA8-661B-4DB0-9E4C-734A65CBA9A3@yahoo.com>
<AF7D9319B29A0242A33C3BF843BD31330EFB8D61@USA7061MS03.na.xerox.net>
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
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: m.p.dankoor@gmail.com, Mike.Relyea@xerox.com
Subject: Re: Lowest 2 items per
In-Reply-To: <4FC90A7F.9020605@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox