Re: obtaining difference between minimum value and next in size

Cheers oliverios and tom for your speedy replies. Unfortunately using v8.3 so the new functions are out. A big credit to oliverios for his sql fu, that seems to do exactly what I want and I think I pretty much understand the query. I always forget the comparison on the rows when thinking about groups.

John
----- Original Message -----
From: Oliveiros d'Azevedo Cristina
To: John Lister ; pgsql-sql(at)postgresql(dot)org
Sent: Wednesday, November 17, 2010 4:09 PM
Subject: Re: [SQL] 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/