Request a Demo
Home   >   mailing lists

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

From Oliveiros
Subject Re: Lowest 2 items per
Date
Msg-id CAGp7Z5Mn17D=7HKqDrsLYmKn=2RLDKM2gaehm3g-iqoqXqNhvg@mail.gmail.com
Whole thread
In response to Re: Lowest 2 items per  (msi77 <msi77@yandex.ru>)
Responses Re: Lowest 2 items per
List pgsql-sql
Tree view
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@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.Relyea@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

pgsql-sql by date:

Previous
From: msi77
Date:
Subject: Re: Lowest 2 items per
Next
From: msi77
Date:
Subject: Re: Lowest 2 items per