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,
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,
On Jun 1, 2012, at 10:34, "Relyea, Mike" 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
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 printersandc
> -Original Message-
> From: David Johnston [mailto:pol...@yahoo.com]
> Sent: Friday, June 01, 2012 11:13 AM
> To: Relyea, Mike
> Cc:
> Subject: Re: [SQL] Lowest 2 items per
>
>
> I would recommend using the "RANK" window function with an appropriate
> partition clause in a sub-query the
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@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 fl
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
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@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,
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 tha
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:
Subject: Re: [SQL] Lowest 2 items per
I would recommend using the "RANK" window function with an appropriate
partition c
> -Original Message-
> From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-
> ow...@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
> -Original Message-
> From: Oliveiros d'Azevedo Cristina
[mailto:oliveiros.crist...@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
12 matches
Mail list logo
