obtaining difference between minimum value and next in size

From: "John Lister" <john(dot)lister-ps(at)kickstone(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: obtaining difference between minimum value and next in size
Date: 2010-11-17 15:11:32
Message-ID: 3BC753A3784F488F98304300B6E437FD@squarepi.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi, I was wondering if it is possible to do this with a single query rather than iterate over all of the rows in an application:

I have a table which for brevity looks like:
create table offers {
integer id;
integer product_id;
double price;
}

where for each product there is a number of offers in this table. Now my question:
Is it possible to obtain the difference between just the minimum price and the next one up per product, so say I have the following data:
id, product_id, price
123, 2, 10.01
125, 2, 10.05
128, 2, 11.30
134, 3, 9.45
147, 3, 11.42
157, 3, 12.08
167, 3, 12.09

then I would like the following returned
product_id, difference
2, .04 (10.05-10.01)
3, 1.97 (11.42-9.45)

,etc

Any ideas?

Thanks

John
--

Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2010-11-17 15:42:36 Re: obtaining difference between minimum value and next in size
Previous Message Achilleas Mantzios 2010-11-17 14:53:53 Re: PostgreSQL array, recursion and more