From john.lister-ps@kickstone.com Wed Nov 17 15:34:22 2010 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-- From tgl@sss.pgh.pa.us Wed Nov 17 15:43:10 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 9D2D31337B7D for ; Wed, 17 Nov 2010 11:43:10 -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 09154-05 for ; Wed, 17 Nov 2010 15:43:04 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id 4C6181337B61 for ; Wed, 17 Nov 2010 11:43:03 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id oAHFgadO027015; Wed, 17 Nov 2010 10:42:36 -0500 (EST) To: "John Lister" cc: pgsql-sql@postgresql.org Subject: Re: obtaining difference between minimum value and next in size In-reply-to: <3BC753A3784F488F98304300B6E437FD@squarepi.com> References: <3BC753A3784F488F98304300B6E437FD@squarepi.com> Comments: In-reply-to "John Lister" message dated "Wed, 17 Nov 2010 15:11:32 +0000" Date: Wed, 17 Nov 2010 10:42:36 -0500 Message-ID: <27014.1290008556@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201011/37 X-Sequence-Number: 34775 "John Lister" writes: > Is it possible to obtain the difference between just the minimum price and the next one up per product, If you're using >= 8.4, try a window function. LEAD or LAG ought to do it. regards, tom lane From akretschmer@spamfence.net Wed Nov 17 16:02:53 2010 Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 824EB1337C2E for ; Wed, 17 Nov 2010 12:02:53 -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 68745-07 for ; Wed, 17 Nov 2010 16:02:46 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from vs137086.vserver.de (static-ip-62-75-137-86.inaddr.intergenia.de [62.75.137.86]) by mail.postgresql.org (Postfix) with ESMTP id 18FBA1337BD7 for ; Wed, 17 Nov 2010 12:02:18 -0400 (AST) Received: (qmail 10006 invoked from network); 17 Nov 2010 17:02:18 +0100 Received: from p57aad14a.dip.t-dialin.net (HELO a-kretschmer.de) (87.170.209.74) by static-ip-62-75-137-86.inaddr.intergenia.de with (AES256-SHA encrypted) SMTP; 17 Nov 2010 17:02:18 +0100 Received: from kretschmer by a-kretschmer.de with local (Exim 4.69) (envelope-from ) id 1PIkSe-00008D-Ib for pgsql-sql@postgresql.org; Wed, 17 Nov 2010 17:02:16 +0100 Date: Wed, 17 Nov 2010 17:02:16 +0100 From: Andreas Kretschmer To: pgsql-sql@postgresql.org Subject: Re: obtaining difference between minimum value and next in size Message-ID: <20101117160216.GA32573@tux> References: <3BC753A3784F488F98304300B6E437FD@squarepi.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <3BC753A3784F488F98304300B6E437FD@squarepi.com> X-OS: Debian/GNU Linux - weil ich es mir Wert bin! X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B 3B86 4DB3 D9EE 7F45 84DA X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the answer is "no"! X-Lugdd: Gerd Kube X-Info: My name is root. Just root. And I am licensed to kill -9 User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201011/38 X-Sequence-Number: 34776 John Lister wrote: > 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? Sure, as Tom Lane pointed out, with >= 8.4: test=*# select * from offers ; 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 (7 Zeilen) Zeit: 0,204 ms test=*# select product_id, price, price - lag(price) over (partition by product_id order by product_id, price), row_number() over (partition by product_id)from offers; product_id | price | ?column? | row_number ------------+-------+----------+------------ 2 | 10.01 | | 1 2 | 10.05 | 0.04 | 2 2 | 11.30 | 1.25 | 3 3 | 9.45 | | 1 3 | 11.42 | 1.97 | 2 3 | 12.08 | 0.66 | 3 3 | 12.09 | 0.01 | 4 (7 Zeilen) Zeit: 0,415 ms test=*# select product_id, price, difference from (select product_id, price, price - lag(price) over (partition by product_id order by product_id, price) as difference, row_number() over (partition by product_id) from offers) foo where row_number <= 2; product_id | price | difference ------------+-------+------------ 2 | 10.01 | 2 | 10.05 | 0.04 3 | 9.45 | 3 | 11.42 | 1.97 (4 Zeilen) Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° From oliveiros.cristina@marktest.pt Wed Nov 17 16:09:24 2010 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-- From john.lister-ps@kickstone.com Wed Nov 17 22:46:34 2010 Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 035111337B5B for ; Wed, 17 Nov 2010 18:46:34 -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 63659-09 for ; Wed, 17 Nov 2010 22:46:26 +0000 (UTC) X-Greylist: from auto-whitelisted 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 A25B71337B61 for ; Wed, 17 Nov 2010 18:46:25 -0400 (AST) Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:4231 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1PIqLH-0005e0-Qd for pgsql-sql@postgresql.org; Wed, 17 Nov 2010 22:19:03 +0000 Message-ID: <183C5FB3760F42DE97D2A5C86E952726@squarepi.com> From: "John Lister" To: References: <3BC753A3784F488F98304300B6E437FD@squarepi.com> <89136F81CFDF42A2B6F9DDC97BF89061@marktestcr.marktest.pt> Subject: Re: obtaining difference between minimum value and next in size Date: Wed, 17 Nov 2010 22:46:21 -0000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001F_01CB86A9.413D6A10" 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/40 X-Sequence-Number: 34778 This is a multi-part message in MIME format. ------=_NextPart_000_001F_01CB86A9.413D6A10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_001F_01CB86A9.413D6A10 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Cheers oliverios and tom for your = speedy replies.=20 Unfortunately using v8.3 so the new functions are out. A big credit to = oliverios=20 for his sql fu, that seems to do exactly what I want and I think I = pretty much=20 understand the query. I always forget the comparison on the rows when = thinking=20 about groups.
 
John
----- Original Message -----
From:=20 Oliveiros d'Azevedo = Cristina=20
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=20 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 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=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 = 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_001F_01CB86A9.413D6A10-- From oliveiros.cristina@marktest.pt Thu Nov 18 10:09:21 2010 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--