Re: Lowest 2 items per

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(at)yandex(dot)ru>

> A few of approaches to solve this problem:
>
> http://sql-ex.com/help/select16.php
>
> 01.06.2012, 18:34, "Relyea, Mike" <Mike(dot)Relyea(at)xerox(dot)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(at)postgresql(dot)org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-sql
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>