Re: obtaining difference between minimum value and next in size - Mailing list pgsql-sql

----- 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/