Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 4D7721337B5B for ; Wed, 17 Nov 2010 12:09:24 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 76947-07 for ; Wed, 17 Nov 2010 16:09:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from jupiter.marktest.pt (delta.marktest.pt [80.251.174.116]) by mail.postgresql.org (Postfix) with ESMTP id 83F9F1337B4F for ; Wed, 17 Nov 2010 12:09:16 -0400 (AST) Received: from Proteu (proteu.marktest.pt [10.61.90.54]) by jupiter.marktest.pt with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id W4GBL55C; Wed, 17 Nov 2010 16:09:14 -0000 Message-ID: <89136F81CFDF42A2B6F9DDC97BF89061@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "John Lister" , References: <3BC753A3784F488F98304300B6E437FD@squarepi.com> Subject: Re: obtaining difference between minimum value and next in size Date: Wed, 17 Nov 2010 16:09:14 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0065_01CB8671.C75E65F0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5512 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.791 tagged_above=-5 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/39 X-Sequence-Number: 34777 This is a multi-part message in MIME format. ------=_NextPart_000_0065_01CB8671.C75E65F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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 -----=20 From: John Lister=20 To: pgsql-sql@postgresql.org=20 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/ ------=_NextPart_000_0065_01CB8671.C75E65F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, John.
 
I am not familiar with the functions = Tom's=20 indicated and I'm sure they constitute a much more straightfoward to = solve your=20 problem.
 
Meanwhile, if you'd like to solve it = with just SQL=20 give this a try and see if it gives you the result you want
 
Best,
Oliveiros
 
SELECT product_id, MIN(pv2) - = pv1
FROM=20 ((
SELECT product_id,MIN(price) as pv1
FROM offers
GROUP BY=20 product_id)  firstSubQuery
NATURAL JOIN
(
SELECT = product_id,price=20 as pv2
FROM offers) secondSubQuery
) total
WHERE  pv1=20 <>  pv2
GROUP BY product_id,pv1
----- Original Message -----
From:=20 John Lister
Sent: Wednesday, November 17, = 2010 3:11=20 PM
Subject: [SQL] obtaining = difference=20 between minimum value and next in size

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=20 offers 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_0065_01CB8671.C75E65F0--