pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLowest 2 items per
15+ messages / 6 participants
[nested] [flat]
* Lowest 2 items per
@ 2012-06-01 14:34 Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 3 replies; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 14:34 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 14:56 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
2 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-06-01 14:56 UTC (permalink / raw)
To: Relyea, Mike <Mike.Relyea@xerox.com>; pgsql-sql
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.Relyea@xerox.com>
To: <pgsql-sql@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 (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 15:13 David Johnston <polobo@yahoo.com>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
2 siblings, 1 reply; 15+ messages in thread
From: David Johnston @ 2012-06-01 15:13 UTC (permalink / raw)
To: Relyea, Mike <Mike.Relyea@xerox.com>; +Cc: pgsql-sql; pgsql-sql
On Jun 1, 2012, at 10:34, "Relyea, Mike" <Mike.Relyea@xerox.com> wrote:
> 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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 15:20 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-06-01 15:20 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; Relyea, Mike <Mike.Relyea@xerox.com>; pgsql-sql
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.cristina@marktest.pt>
To: "Relyea, Mike" <Mike.Relyea@xerox.com>; <pgsql-sql@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.Relyea@xerox.com>
> To: <pgsql-sql@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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 15:44 Relyea, Mike <Mike.Relyea@xerox.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 15:44 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-sql
> -----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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 16:17 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 16:17 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.cristina@marktest.pt]
> Sent: Friday, June 01, 2012 11:21 AM
> To: Oliveiros d'Azevedo Cristina; Relyea, Mike;
pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>
> 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;
>
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;
Mike
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 16:28 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-06-01 16:28 UTC (permalink / raw)
To: Relyea, Mike <Mike.Relyea@xerox.com>; +Cc: pgsql-sql
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
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 16:35 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 1 reply; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 16:35 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.cristina@marktest.pt]
> Sent: Friday, June 01, 2012 12:28 PM
> To: Relyea, Mike
> Cc: pgsql-sql@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.
Mike
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 16:58 Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 1 reply; 15+ messages in thread
From: Oliveiros d'Azevedo Cristina @ 2012-06-01 16:58 UTC (permalink / raw)
To: Relyea, Mike <Mike.Relyea@xerox.com>; +Cc: pgsql-sql
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
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
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
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 18:31 Mario Dankoor <m.p.dankoor@gmail.com>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
0 siblings, 1 reply; 15+ messages in thread
From: Mario Dankoor @ 2012-06-01 18:31 UTC (permalink / raw)
To: Relyea, Mike <Mike.Relyea@xerox.com>; +Cc: pgsql-sql
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
)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 19:55 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Mario Dankoor <m.p.dankoor@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 19:55 UTC (permalink / raw)
To: Mario Dankoor <m.p.dankoor@gmail.com>; +Cc: pgsql-sql
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of Mario Dankoor
> Sent: Friday, June 01, 2012 2:31 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> 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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-01 20:04 Relyea, Mike <Mike.Relyea@xerox.com>
parent: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
0 siblings, 0 replies; 15+ messages in thread
From: Relyea, Mike @ 2012-06-01 20:04 UTC (permalink / raw)
To: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>; +Cc: pgsql-sql
> -----Original Message-----
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.cristina@marktest.pt]
> Sent: Friday, June 01, 2012 12:59 PM
> To: Relyea, Mike
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] Lowest 2 items per
>
> * I see...
>
> If we add a query with a union that selects only the single ink
printers.
>
> Something like
>
> 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
> 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
>
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.
Mike
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-02 13:56 msi77 <msi77@yandex.ru>
parent: Relyea, Mike <Mike.Relyea@xerox.com>
2 siblings, 1 reply; 15+ messages in thread
From: msi77 @ 2012-06-02 13:56 UTC (permalink / raw)
To: Relyea, Mike <mike.relyea@xerox.com>; +Cc: pgsql-sql
A few of approaches to solve this problem:
http://sql-ex.com/help/select16.php
01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>:
> 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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-02 15:00 Oliveiros <oliveiros.cristina@gmail.com>
parent: msi77 <msi77@yandex.ru>
0 siblings, 1 reply; 15+ messages in thread
From: Oliveiros @ 2012-06-02 15:00 UTC (permalink / raw)
To: msi77 <msi77@yandex.ru>; +Cc: Relyea, Mike <mike.relyea@xerox.com>; pgsql-sql
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 <msi77@yandex.ru>
> A few of approaches to solve this problem:
>
> http://sql-ex.com/help/select16.php
>
> 01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>:
> > 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 (pgsql-sql@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Lowest 2 items per
@ 2012-06-02 16:32 msi77 <msi77@yandex.ru>
parent: Oliveiros <oliveiros.cristina@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: msi77 @ 2012-06-02 16:32 UTC (permalink / raw)
To: Oliveiros <oliveiros.cristina@gmail.com>; +Cc: pgsql-sql
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.cristina@gmail.com>:
> 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 <msi77@yandex.ru>
>> A few of approaches to solve this problem:
>>
>> http://sql-ex.com/help/select16.php
>>
>> 01.06.2012, 18:34, "Relyea, Mike" <Mike.Relyea@xerox.com>:
>>> 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 (pgsql-sql@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>> --
>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2012-06-02 16:32 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-01 14:34 Lowest 2 items per Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-01 14:56 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-06-01 15:20 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-06-01 16:17 ` Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-01 16:28 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-06-01 16:35 ` Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-01 16:58 ` Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
2012-06-01 20:04 ` Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-01 15:13 ` David Johnston <polobo@yahoo.com>
2012-06-01 15:44 ` Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-01 18:31 ` Mario Dankoor <m.p.dankoor@gmail.com>
2012-06-01 19:55 ` Relyea, Mike <Mike.Relyea@xerox.com>
2012-06-02 13:56 ` msi77 <msi77@yandex.ru>
2012-06-02 15:00 ` Oliveiros <oliveiros.cristina@gmail.com>
2012-06-02 16:32 ` msi77 <msi77@yandex.ru>
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