Home > mailing lists

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

From	John Lister
Subject	obtaining difference between minimum value and next in size
Date	November 17, 2010 14:34:22
Msg-id	3BC753A3784F488F98304300B6E437FD@squarepi.com Whole thread Raw
Responses	Re: obtaining difference between minimum value and next in size (Tom Lane <tgl@sss.pgh.pa.us>) Re: obtaining difference between minimum value and next in size (Andreas Kretschmer <akretschmer@spamfence.net>)
List	pgsql-sql

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

From: Achilleas Mantzios
Date: 17 November 2010, 13:56:52
Subject: Re: PostgreSQL array, recursion and more

From: Tom Lane
Date: 17 November 2010, 14:43:11
Subject: Re: obtaining difference between minimum value and next in size