Hi,
I have time series data: price(id_price int, price int, created_on timestamp)
I'd like to select the latest price before, say, 2010-03-10 and the
latest price after that date.
Using "group by" and self-joins I was able to build a (quite large :)
working query.
But I wonder if there is a cleaner, shorter solution with a window
function.
I tried something like:
select * from (select
first_value(p.id_price) over w as first_id_price,
first_value(p.price) over w as first_price,
first_value(p.created_on::date) over w as first_date,
nth_value(p.id_price,2) over w as second_id_price,
nth_value(p.price,2) over w as second_price,
nth_value(p.created_on::date,2) over w as second_date,
p.id_price
from price p
window w as (order by p.created_on > '2010-03-10, p.id_price desc
rows between unbounded preceding and unbounded following))
as t where first_id_price=id_price;
But this doesn't return correct results.
Thanks for any suggestions,
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
