Re: obtaining difference between minimum value and next in size

Поиск
Список
Период
Сортировка
От Oliveiros d'Azevedo Cristina
Тема Re: obtaining difference between minimum value and next in size
Дата
Msg-id 89136F81CFDF42A2B6F9DDC97BF89061@marktestcr.marktest.pt
обсуждение исходный текст
Ответ на obtaining difference between minimum value and next in size  ("John Lister" <john.lister-ps@kickstone.com>)
Список pgsql-sql
Дерево обсуждения
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 -----
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/

В списке pgsql-sql по дате отправления: