Re: obtaining difference between minimum value and next in size

Hi, John.

I am not familiar with the functions Tom's indicated and I'm sure they constitute a much more straightfoward to solve your problem.

Meanwhile, if you'd like to solve it with just SQL give this a try and see if it gives you the result you want

Best,
Oliveiros

SELECT product_id, MIN(pv2) - pv1
FROM ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY product_id) firstSubQuery
NATURAL JOIN
(
SELECT product_id,price as pv2
FROM offers) secondSubQuery
) total
WHERE pv1 <> pv2
GROUP BY product_id,pv1
----- Original Message -----
From: John Lister
To: pgsql-sql(at)postgresql(dot)org
Sent: Wednesday, November 17, 2010 3:11 PM
Subject: [SQL] obtaining difference between minimum value and next in size

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/