window function to sort times series data? - Mailing list pgsql-sql

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_pricefromprice p   window w as (order by p.created_on > '2010-03-10, p.id_price desc   rows between unbounded
precedingand unbounded following))    as t where first_id_price=id_price;
 

But this doesn't return correct results.

Thanks for any suggestions,