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 <ms...@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.rel...@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 >
