From: Oliveiros d'Azevedo Cristina <oliveiros.cristina@marktest.pt>
To: John Lister <john.lister-ps@kickstone.com>
To: pgsql-sql@postgresql.org
Subject: Re: obtaining difference between minimum value and next in size
Date: Thu, 18 Nov 2010 10:09:11 -0000
Message-ID: <684FFBCA0637413987A29036F013A816@marktestcr.marktest.pt> (raw)
References: <3BC753A3784F488F98304300B6E437FD@squarepi.com>
<89136F81CFDF42A2B6F9DDC97BF89061@marktestcr.marktest.pt>
<183C5FB3760F42DE97D2A5C86E952726@squarepi.com>
John,
Great to hear it helped you out
Best,
Oliver
----- Original Message -----
From: John Lister
To: pgsql-sql@postgresql.org
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 -----
From: Oliveiros d'Azevedo Cristina
To: John Lister ; pgsql-sql@postgresql.org
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 -----
From: John Lister
To: pgsql-sql@postgresql.org
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: oliveiros.cristina@marktest.pt, john.lister-ps@kickstone.com
Subject: Re: obtaining difference between minimum value and next in size
In-Reply-To: <684FFBCA0637413987A29036F013A816@marktestcr.marktest.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox