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--