Re: Filling null values

hi,

try this. If your table name is mytable:

select
       a.homeid
     , a.city
     , coalesce(a.date, (select b.date from mytable b where
b.homeid=a.homeid and b.date is not null order by b.prepost=a.prepost
desc limit 1) ) as date
     , a.measurement
     , a.prepost
from
     mytable a



Thomas

Am 05.08.2011 18:32, schrieb jeffrey:
> 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?
>
> Thanks,
> Jeff
>