On 2012-06-01 5:44 PM, Relyea, Mike wrote:
-----Original Message-----
From: David Johnston [mailto:pol...@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
)
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
