Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 7F1A31337B53 for ; Wed, 17 Nov 2010 11:34:22 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 48015-01 for ; Wed, 17 Nov 2010 15:34:11 +0000 (UTC) X-Greylist: delayed 00:22:35.860286 by SQLgrey-1.7.6 Received: from mail.kickstone.com (smtp.kickstone.co.uk [87.194.183.70]) by mail.postgresql.org (Postfix) with ESMTP id 7458B1337B56 for ; Wed, 17 Nov 2010 11:34:10 -0400 (AST) Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:4906 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1PIjFB-0005O2-TO for pgsql-sql@postgresql.org; Wed, 17 Nov 2010 14:44:17 +0000 Message-ID: <3BC753A3784F488F98304300B6E437FD@squarepi.com> From: "John Lister" To: Subject: obtaining difference between minimum value and next in size Date: Wed, 17 Nov 2010 15:11:32 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0009_01CB8669.B8162C20" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5931 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5931 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201011/36 X-Sequence-Number: 34774 This is a multi-part message in MIME format. ------=_NextPart_000_0009_01CB8669.B8162C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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/ ------=_NextPart_000_0009_01CB8669.B8162C20 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi, I was wondering if it is possible = to do this=20 with a single query rather than iterate over all of the rows in an=20 application:

I have a table which for brevity looks=20 like:

create table offers {

integer id;

integer product_id;

double = price;

}

where for each product there is a = number of offers=20 in this table. Now my question:

Is it possible to obtain the difference = between=20 just the minimum price and the next one up per product, so say I have = the=20 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=20 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/<= BR>

------=_NextPart_000_0009_01CB8669.B8162C20--