-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of jeffrey Sent: Friday, August 05, 2011 12:33 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: Filling null values I have a table that looks like this: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco NULL 1932 post 124 los angeles 2/4/2005 938 pre 124 NULL NULL 266 pre 124 los angeles 7/4/2006 777 post I'd like to write a query so that I get the following result: homeid city date measurement pre/post 123 san francisco 1/2/2003 1458 pre 123 san francisco 1/2/2003 1932 post 124 los angeles 2/4/2005 938 pre 124 los angeles 2/4/2005 266 pre 124 los angeles 7/4/2006 777 post If a city or date is null, then it will fill from other not null values with the same homeid. If given the choice, it will preferentially fill from a row where homeid AND pre/post match. But if that doesn't match, then it will still fill from the same homeid. Does anyone have ideas for this? --------------------------------------------------------------- What version are you using? You may be able to accomplish your goals with Window functions. If "homeid" determines "city" you should just remove "city" from the table altogether and create a lookup table where "homeid" is the PK and "city" is one of the other columns. Your date issue is somewhat more problematic to correctly address. As an alternative to Window functions you'd probably want to, generally, create lookup tables. I just described the "homeid" lookup table but the date lookup table is a little more complicated. The general idea would be to ORDER and NUMBER the current records and then create a master lookup using ROW_NUMBER, "homeid", and "date". Then, for any rows missing a date you point into the lookup table and use the date from the lookup record with the largest ROW_NUMBER less than the current row. David J. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
