Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 476381337B5B for ; Thu, 18 Nov 2010 06:09:21 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 41946-09 for ; Thu, 18 Nov 2010 10:09:14 +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 15F491337B77 for ; Thu, 18 Nov 2010 06:09:12 -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 W4GBL0QZ; Thu, 18 Nov 2010 10:09:12 -0000 Message-ID: <684FFBCA0637413987A29036F013A816@marktestcr.marktest.pt> From: "Oliveiros d'Azevedo Cristina" To: "John Lister" , References: <3BC753A3784F488F98304300B6E437FD@squarepi.com> <89136F81CFDF42A2B6F9DDC97BF89061@marktestcr.marktest.pt> <183C5FB3760F42DE97D2A5C86E952726@squarepi.com> Subject: Re: obtaining difference between minimum value and next in size Date: Thu, 18 Nov 2010 10:09:11 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0039_01CB8708.A5727160" 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/41 X-Sequence-Number: 34779 This is a multi-part message in MIME format. ------=_NextPart_000_0039_01CB8708.A5727160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable John, Great to hear it helped you out Best, Oliver ----- Original Message -----=20 From: John Lister=20 To: pgsql-sql@postgresql.org=20 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 -----=20 From: Oliveiros d'Azevedo Cristina=20 To: John Lister ; pgsql-sql@postgresql.org=20 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 -----=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_0039_01CB8708.A5727160 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
John,
 
Great to hear it helped you = out
 
Best,
Oliver
----- Original Message -----
From:=20 John Lister
Sent: Wednesday, November 17, = 2010 10:46=20 PM
Subject: Re: [SQL] obtaining = difference=20 between minimum value and next in size

Cheers oliverios and tom for your = speedy replies.=20 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=20 pretty much understand the query. I always forget the comparison on = the rows=20 when thinking about groups.
 
John
----- Original Message -----
From:=20 Oliveiros d'Azevedo=20 Cristina
To: John Lister ; pgsql-sql@postgresql.org =
Sent: Wednesday, November 17, = 2010 4:09=20 PM
Subject: Re: [SQL] obtaining = difference=20 between minimum value and next in size

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=20 your problem.
 
Meanwhile, if you'd like to solve = it with just=20 SQL 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 = product_id)  firstSubQuery
NATURAL JOIN
(
SELECT=20 product_id,price as pv2
FROM offers) secondSubQuery
)=20 total
WHERE  pv1 <>  pv2
GROUP BY=20 product_id,pv1
----- Original Message ----- =
From:=20 John Lister =
Sent: Wednesday, November = 17, 2010=20 3:11 PM
Subject: [SQL] obtaining = difference=20 between minimum value and next in size

Hi, I was wondering if it is = possible to do=20 this 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=20 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=20 between just the minimum price and the next one up per product, so = say I=20 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=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_0039_01CB8708.A5727160--