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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
