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

[Oliveiros d'Azevedo Cristina]

John,

Great to hear it helped you out

Best,
Oliver
  ----- Original Message ----- 
  From: John Lister 
  To: pgsql-sql@postgresql.org 
  Sent: Wednesday, November 17, 2010 10:46 PM
  Subject: Re: [SQL] 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@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/