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

[John Lister]

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/