> -----Original Message-----
> From: [email protected] [mailto:pgsql-sql-
> [email protected]] On Behalf Of Mario Dankoor
> Sent: Friday, June 01, 2012 2:31 PM
> To: Relyea, Mike
> Cc: [email protected]
> Subject: Re: [SQL] Lowest 2 items per
> 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
> )
Mario,
This works quite nicely! I had to add a few criteria to it and the
results it gives does have some ties that I need to figure out how to
break - but that'll be easy because if there is a tie then I don't care
which one wins. Here's the working query that I am going to modify a
little bit more.
SELECT FRS.* FROM (
SELECT PRN.Make
,PRN.Model
,CSM.Color
,CSM.Type
,CSM.PartNumber
,Cost/Yield as rank
FROM Consumables CSM
,Printers PRN
,PrintersAndConsumables PCM
WHERE 1 = 1
AND PCM.PrinterID = PRN.PrinterID
AND PCM.ConsumableID = CSM.ConsumableID
group by PRN.Make
,PRN.Model
,CSM.Color
,CSM.Type
,CSM.Cost
,CSM.Yield
,CSM.PartNumber
) FRS
WHERE 3 > (
SELECT COUNT(*)
FROM (
SELECT PRN.Make
,PRN.Model
,CSM.Color
,CSM.Type
,Cost/Yield as rank
FROM Consumables CSM
,Printers PRN
,PrintersAndConsumables PCM
WHERE 1 = 1
AND PCM.PrinterID = PRN.PrinterID
AND PCM.ConsumableID = CSM.ConsumableID
group by PRN.Make
,PRN.Model
,CSM.Color
,CSM.Type
,CSM.Cost
,CSM.Yield
) 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.rank <= FRS.rank
) AND
rank IS NOT NULL
ORDER BY Make, Model, Color, Type;
Thanks for the help!
Mike
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
