Skip site navigation (1) Skip section navigation (2)

Re: Lowest 2 items per

From: "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)com>,<pgsql-sql(at)postgresql(dot)org>
Subject: Re: Lowest 2 items per
Date: 2012-06-01 14:56:20
Message-ID: 1A73A33A6D424324B55B126B61DC4E2A@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: 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(dot)Relyea(at)xerox(dot)com>
To: <pgsql-sql(at)postgresql(dot)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

In response to

Responses

pgsql-sql by date

Next:From: David JohnstonDate: 2012-06-01 15:13:27
Subject: Re: Lowest 2 items per
Previous:From: Relyea, MikeDate: 2012-06-01 14:34:30
Subject: Lowest 2 items per

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group