Re: Lowest 2 items per - Mailing list pgsql-sql

Nice resource, msi77.<br /><br />Thanx for sharing.<br /><br />I wasn't aware of none of these techniques, actually, so
Itried to start from scratch, but I should've realized that many people in the past had the same problem as Mike and I
shouldhave googled a little instead of trying to re-invent the wheel.<br /><br />Anyway, this is great information and
I'msure it will be useful in the future.<br />Again thanx for sharing.<br /><br />Best,<br />Oliver<br /><br /><br
/><br/><div class="gmail_quote">2012/6/2 msi77 <span dir="ltr"><<a href="mailto:msi77@yandex.ru"
target="_blank">msi77@yandex.ru</a>></span><br/><blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px#ccc solid;padding-left:1ex">A few of approaches to solve this problem:<br /><br /><a
href="http://sql-ex.com/help/select16.php"target="_blank">http://sql-ex.com/help/select16.php</a><br /><br />
01.06.2012,18:34, "Relyea, Mike" <<a href="mailto:Mike.Relyea@xerox.com">Mike.Relyea@xerox.com</a>>:<br /><div
class="HOEnZb"><divclass="h5">> I need a little help putting together a query.  I have the tables listed<br /> >
belowand I need to return the lowest two consumables (ranked by cost<br /> > divided by yield) per printer, per
colorof consumable, per type of<br /> > consumable.<br /> ><br /> > CREATE TABLE printers<br /> > (<br />
>  printerid serial NOT NULL,<br /> >   make text NOT NULL,<br /> >   model text NOT NULL,<br /> >
  CONSTRAINTprinters_pkey PRIMARY KEY (make , model ),<br /> >   CONSTRAINT printers_printerid_key UNIQUE (printerid
),<br/> > )<br /> ><br /> > CREATE TABLE consumables<br /> > (<br /> >   consumableid serial NOT
NULL,<br/> >   brand text NOT NULL,<br /> >   partnumber text NOT NULL,<br /> >   color text NOT NULL,<br />
>  type text NOT NULL,<br /> >   yield integer,<br /> >   cost double precision,<br /> >   CONSTRAINT
consumables_pkeyPRIMARY KEY (brand , partnumber ),<br /> >   CONSTRAINT consumables_consumableid_key UNIQUE
(consumableid)<br /> > )<br /> ><br /> > CREATE TABLE printersandconsumables<br /> > (<br /> >
  printeridinteger NOT NULL,<br /> >   consumableid integer NOT NULL,<br /> >   CONSTRAINT
printersandconsumables_pkeyPRIMARY KEY (printerid ,<br /> > consumableid ),<br /> >   CONSTRAINT
printersandconsumables_consumableid_fkeyFOREIGN KEY<br /> > (consumableid)<br /> >       REFERENCES consumables
(consumableid)MATCH SIMPLE<br /> >       ON UPDATE CASCADE ON DELETE CASCADE,<br /> >   CONSTRAINT
printersandconsumables_printerid_fkeyFOREIGN KEY<br /> > (printerid)<br /> >       REFERENCES printers
(printerid)MATCH SIMPLE<br /> >       ON UPDATE CASCADE ON DELETE CASCADE<br /> > )<br /> ><br /> > I've
pulledtogether this query which gives me the lowest consumable per<br /> > printer per color per type, but I need
thelowest two not just the first<br /> > lowest.<br /> ><br /> > SELECT printers.make, printers.model,
consumables.color,<br/> > consumables.type, min(cost/yield) AS cpp<br /> > FROM printers<br /> > JOIN
printersandconsumablesON printers.printerid =<br /> > printersandconsumables.printerid<br /> > JOIN consumables
ONconsumables.consumableid =<br /> > printersandconsumables.consumableid<br /> > WHERE consumables.cost Is Not
Null<br/> > AND consumables.yield Is Not Null<br /> > GROUP BY printers.make, printers.model,
consumables.color,<br/> > consumables.type<br /> > ORDER BY make, model;<br /> ><br /> > After doing a
googlesearch I didn't come up with anything that I was<br /> > able to use so I'm asking you fine folks!<br />
><br/> > Mike<br /> ><br /> > --<br /> > Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br/> > To make changes to your subscription:<br
/>> <a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /> --<br /> Sent via pgsql-sql mailing list
(<ahref="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>)<br /> To make changes to your subscription:<br
/><ahref="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></div></div></blockquote></div><br />