Skip site navigation (1) Skip section navigation (2)

Re: obtaining difference between minimum value and next in size

From: "John Lister" <john(dot)lister-ps(at)kickstone(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: obtaining difference between minimum value and next in size
Date: 2010-11-17 22:46:21
Message-ID: 183C5FB3760F42DE97D2A5C86E952726@squarepi.com (view raw or flat)
Thread:
Lists: pgsql-sql
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/

In response to

Responses

pgsql-sql by date

Next:From: Oliveiros d'Azevedo CristinaDate: 2010-11-18 10:09:11
Subject: Re: obtaining difference between minimum value and next in size
Previous:From: Oliveiros d'Azevedo CristinaDate: 2010-11-17 16:09:14
Subject: Re: obtaining difference between minimum value and next in size

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group