Re: [SQL] obtaining difference between minimum value and next in size

[Oliveiros d'Azevedo Cristina]

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/