[SQL] Lowest 2 items per

[Relyea, Mike]

I need a little help putting together a query. I have the tables listed
below and I need to return the lowest two consumables (ranked by cost
divided by yield) per printer, per color of consumable, per type of
consumable.

CREATE TABLE printers
(
printerid serial NOT NULL,
make text NOT NULL,
model text NOT NULL,
CONSTRAINT printers_pkey PRIMARY KEY (make , model ),
CONSTRAINT printers_printerid_key UNIQUE (printerid ),
)

CREATE TABLE consumables
(
consumableid serial NOT NULL,
brand text NOT NULL,
partnumber text NOT NULL,
color text NOT NULL,
type text NOT NULL,
yield integer,
cost double precision,
CONSTRAINT consumables_pkey PRIMARY KEY (brand , partnumber ),
CONSTRAINT consumables_consumableid_key UNIQUE (consumableid )
)

CREATE TABLE printersandconsumables
(
printerid integer NOT NULL,
consumableid integer NOT NULL,
CONSTRAINT printersandconsumables_pkey PRIMARY KEY (printerid ,
consumableid ),
CONSTRAINT printersandconsumables_consumableid_fkey FOREIGN KEY
(consumableid)
REFERENCES consumables (consumableid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT printersandconsumables_printerid_fkey FOREIGN KEY
(printerid)
REFERENCES printers (printerid) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)

I've pulled together this query which gives me the lowest consumable per
printer per color per type, but I need the lowest two not just the first
lowest.

SELECT printers.make, printers.model, consumables.color,
consumables.type, min(cost/yield) AS cpp
FROM printers
JOIN printersandconsumables ON printers.printerid =
printersandconsumables.printerid
JOIN consumables ON consumables.consumableid =
printersandconsumables.consumableid
WHERE consumables.cost Is Not Null
AND consumables.yield Is Not Null
GROUP BY printers.make, printers.model, consumables.color,
consumables.type
ORDER BY make, model;


After doing a google search I didn't come up with anything that I was
able to use so I'm asking you fine folks!

Mike

--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Oliveiros d'Azevedo Cristina]

Hi, Mike,

Can you tell me if this gives what you want, and if it doesn't, what is the
error reported, or wrong result ?

This is untested query, so Im not sure about it.

Best,
Oliver

SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
(

SELECT printers.make, printers.model, consumables.color,
consumables.type, min(cost/yield) AS cpp
FROM printers
JOIN printersandconsumables ON printers.printerid =
printersandconsumables.printerid
JOIN consumables ON consumables.consumableid =
printersandconsumables.consumableid
WHERE consumables.cost Is Not Null
AND consumables.yield Is Not Null
GROUP BY printers.make, printers.model, consumables.color,
consumables.type

) subquery1
NATURAL JOIN
(
SELECT printers.make, printers.model, consumables.color,
consumables.type

FROM printers
JOIN printersandconsumables ON printers.printerid =
printersandconsumables.printerid
JOIN consumables ON consumables.consumableid =
printersandconsumables.consumableid
WHERE consumables.cost Is Not Null
AND consumables.yield Is Not Null

) subquery2
WHERE subquery2.cost / subquery2.yield <> subquery1.cpp
GROUP BY make, model, color,type
ORDER BY make, model;

[David Johnston]

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.

[Oliveiros d'Azevedo Cristina]

Sorry, Mike, previous query was flawed.

This is (hopefully) the correct version

Best,
Oliver

SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
(

SELECT printers.make, printers.model, consumables.color,
consumables.type, min(cost/yield) AS cpp
FROM printers
JOIN printersandconsumables ON printers.printerid =
printersandconsumables.printerid
JOIN consumables ON consumables.consumableid =
printersandconsumables.consumableid
WHERE consumables.cost Is Not Null
AND consumables.yield Is Not Null
GROUP BY printers.make, printers.model, consumables.color,
consumables.type

) subquery1

JOIN
(
SELECT printers.make, printers.model, consumables.color,

consumables.type,cost,yield

FROM printers
JOIN printersandconsumables ON printers.printerid =
printersandconsumables.printerid
JOIN consumables ON consumables.consumableid =
printersandconsumables.consumableid
WHERE consumables.cost Is Not Null
AND consumables.yield Is Not Null

) subquery2
ON (subquery1.make = subquery2.make
AND subquery1.model = subquery2.model
AND subquery1.color = subquery2.color
AND subquery1.type = subquery2.type)

WHERE subquery2.cost / subquery2.yield <> subquery1.cpp

GROUP BY subquery2.make,subquery2. model,
subquery2.color,subquery2.type,subquery1.cpp

ORDER BY make, model;

----- Original Message -----
From: "Oliveiros d'Azevedo Cristina" <oliveiros...@marktest.pt>
To: "Relyea, Mike" <Mike....@xerox.com>; <pgsq...@postgresql.org>
Sent: Friday, June 01, 2012 3:56 PM
Subject: Re: [SQL] Lowest 2 items per


> Hi, Mike,
>
> Can you tell me if this gives what you want, and if it doesn't, what is
> the error reported, or wrong result ?
>
> This is untested query, so Im not sure about it.
>
> Best,
> Oliver
>
> SELECT make, model, color,type, subquery1.cpp, min(cost/yield) as cpp2
> (

> SELECT printers.make, printers.model, consumables.color,
> consumables.type, min(cost/yield) AS cpp
> FROM printers
> JOIN printersandconsumables ON printers.printerid =
> printersandconsumables.printerid
> JOIN consumables ON consumables.consumableid =
> printersandconsumables.consumableid
> WHERE consumables.cost Is Not Null
> AND consumables.yield Is Not Null
> GROUP BY printers.make, printers.model, consumables.color,
> consumables.type

> ) subquery1
> NATURAL JOIN
> (
> SELECT printers.make, printers.model, consumables.color,
> consumables.type

> FROM printers
> JOIN printersandconsumables ON printers.printerid =
> printersandconsumables.printerid
> JOIN consumables ON consumables.consumableid =
> printersandconsumables.consumableid
> WHERE consumables.cost Is Not Null
> AND consumables.yield Is Not Null

> ) subquery2
> WHERE subquery2.cost / subquery2.yield <> subquery1.cpp
> GROUP BY make, model, color,type
> ORDER BY make, model;
>
>
> ----- Original Message -----
> From: "Relyea, Mike" <Mike....@xerox.com>
> To: <pgsq...@postgresql.org>
> Sent: Friday, June 01, 2012 3:34 PM
> Subject: [SQL] Lowest 2 items per
>
>

[Relyea, Mike]

> -----Original Message-----
> From: David Johnston [mailto:pol...@yahoo.com]
> Sent: Friday, June 01, 2012 11:13 AM
> To: Relyea, Mike
> Cc: <pgsq...@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.

[Relyea, Mike]

> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina

[mailto:oliveiros...@marktest.pt]
> Sent: Friday, June 01, 2012 11:21 AM
> To: Oliveiros d'Azevedo Cristina; Relyea, Mike;
pgsq...@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>

Oliver,

I had to make a few grammatical corrections on your query to get it to
run, but once I did it gave me almost correct results. It leaves out
all of the printer models that only have one consumable with a cost.
Some printers might have more than two black inks and some might have
only one. Your query only returns those printers that have two or more.

Here's your query with the corrections I had to make
SELECT subquery2.Make, subquery2.Model, subquery2.Color,subquery2.Type,
subquery1.cpp, min(Cost/Yield) as cpp2
FROM( SELECT Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type, min(Cost/Yield) AS cpp FROM Printers JOIN
PrintersAndConsumables ON Printers.PrinterID =
PrintersAndConsumables.PrinterID JOIN Consumables ON
Consumables.ConsumableID = PrintersAndConsumables.ConsumableID
WHERE Consumables.Cost Is Not Null
AND Consumables.Yield Is Not Null
GROUP BY Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type
) subquery1
JOIN
(
SELECT Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type,Cost,Yield FROM Printers JOIN PrintersAndConsumables
ON Printers.PrinterID = PrintersAndConsumables.PrinterID JOIN
Consumables ON Consumables.ConsumableID =
PrintersAndConsumables.ConsumableID
WHERE Consumables.Cost Is Not Null
AND Consumables.Yield Is Not Null
) subquery2
ON (subquery1.Make = subquery2.Make
AND subquery1.Model = subquery2.Model
AND subquery1.Color = subquery2.Color
AND subquery1.Type = subquery2.Type)
WHERE subquery2.Cost / subquery2.Yield <> subquery1.cpp GROUP BY
subquery2.Make,subquery2.Model,
subquery2.Color,subquery2.Type,subquery1.cpp
ORDER BY Make, Model;

[Oliveiros d'Azevedo Cristina]

* Hello again, Mike,

Thank you for your e-mail.

Yes, you are right, now, thinking about the way I built it, the query,
indeed, leaves out the corner case of models which have just one
consumable.

I didn't try ur version of the query.
Does itork now with your improvements ?
Or were they only gramatical ?

Best,
Oliver

[Relyea, Mike]

> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros...@marktest.pt]
> Sent: Friday, June 01, 2012 12:28 PM
> To: Relyea, Mike
> Cc: pgsq...@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>

> Yes, you are right, now, thinking about the way I built it, the query,
indeed,
> leaves out the corner case of models which have just one consumable.
>
> I didn't try ur version of the query.
> Does itork now with your improvements ?
> Or were they only gramatical ?
>
> Best,
> Oliver

I only made grammatical changes necessary for the query to function
(adding a missing FROM, fully qualifying "SELECT Make" as " SELECT
subquery2.Make", etc.)
I tried changing the join type to right and left but that did not have
the desired result.

[Oliveiros d'Azevedo Cristina]

I only made grammatical changes necessary for the query to function
(adding a missing FROM, fully qualifying "SELECT Make" as " SELECT
subquery2.Make", etc.)
I tried changing the join type to right and left but that did not have
the desired result.

* I see...

If we add a query with a union that selects only the single ink printers.

Something like

UNION
SELECT Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type, min(Cost/Yield) AS cpp,min(Cost/Yield) AS cpp2

FROM Printers JOIN
PrintersAndConsumables ON Printers.PrinterID =
PrintersAndConsumables.PrinterID JOIN Consumables ON
Consumables.ConsumableID = PrintersAndConsumables.ConsumableID
WHERE Consumables.Cost Is Not Null
AND Consumables.Yield Is Not Null
GROUP BY Printers.Make, Printers.Model, Consumables.Color,
Consumables.Type

HAVING COUNT(*)=1
ORDER BY Make, Model;

Can this be the results we're after
?

Best,
Oliver

[Mario Dankoor]

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:<pgsq...@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
)

[Relyea, Mike]

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

[Relyea, Mike]

> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros...@marktest.pt]

> Sent: Friday, June 01, 2012 12:59 PM
> To: Relyea, Mike
> Cc: pgsq...@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>

Oliver,

Thanks for your help. You gave me a workable query. I made a few minor
changes to your idea but I really like the solution offered by Mario.
It provides more flexibility and is cleaner. For example, with Mario's
I can take the lowest 3 easily instead of just the lowest 2.

[msi77]

A few of approaches to solve this problem:

http://sql-ex.com/help/select16.php

01.06.2012, 18:34, "Relyea, Mike" <Mike....@xerox.com>:

> SELECT printers.make, printers.model, consumables.color,
> consumables.type, min(cost/yield) AS cpp
> FROM printers
> JOIN printersandconsumables ON printers.printerid =
> printersandconsumables.printerid
> JOIN consumables ON consumables.consumableid =
> printersandconsumables.consumableid
> WHERE consumables.cost Is Not Null

> AND consumables.yield Is Not Null
> GROUP BY printers.make, printers.model, consumables.color,
> consumables.type
> ORDER BY make, model;
>
> After doing a google search I didn't come up with anything that I was
> able to use so I'm asking you fine folks!
>

[Oliveiros]

Nice resource, msi77.

Thanx for sharing.

I wasn't aware of none of these techniques, actually, so I tried to start from scratch, but I should've realized that many people in the past had the same problem as Mike and I should have googled a little instead of trying to re-invent the wheel.

Anyway, this is great information and I'm sure it will be useful in the future.
Again thanx for sharing.

Best,
Oliver

2012/6/2 msi77 <ms...@yandex.ru>

[msi77]

Thank you for reply, Oliver.
I want that you'll pay attention to the learn exercises which can by made under PostgreSQL among few other DBMS:
http://sql-ex.ru/exercises/index.php?act=learn

02.06.2012, 19:00, "Oliveiros" <oliveiros...@gmail.com>: