John Lister <john.lister...@kickstone.com> 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° -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql