Re: [SQL] Lowest 2 items per

[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;


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


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


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

[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

[Oliveiros d'Azevedo Cristina]

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;

* 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

[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

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

[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>:

[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>: