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@postgresql.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@postgresql.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/