McGehee, Robert wrote > Hello, > I have a table with dates and stock prices. Some of the prices are NULL > because the stock did not trade on that day. In such instances, I'd like > to replace the NULL value with the most recent non-NULL value, but I can't > find an efficient way to do this.
Cannot speak to efficiency but something like this may work for you: self-contained SQL: WITH input_src (id, idx, price) AS ( VALUES (1,1,'101'),(1,2,NULL),(1,3,NULL),(1,4,'104'),(1,5,NULL) ) , construct_possibles AS ( SELECT *, array_agg(price) OVER ( PARTITION BY id ORDER BY idx ROWS 3 PRECEDING --# attempts to limit size of array by only going back a limited number of days ) AS possibles FROM input_src ORDER BY idx ASC ) SELECT * --, some_function_to_get_most_recent_nonnull_value(possibles) FROM construct_possibles ; HTH David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Carry-forward-last-observation-tp5759988p5760005.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
