Bob ----

That is a very impressive answer. Far as my vote is concerned, you won the challenge.


>>I have a table with null values in fields here and there. I would like a query that fills these in with values from the most recent record with something in that field. For example, if I have:
>>

>>field1     field2     field3
>>
>>3          5          6
>>5          6
>>7                     2
>>                      9
>>2          1          8
>>3          2
>>

>>I want:
>>

>>field1     field2     field3
>>
>>3          5          6
>>5          6          6
>>7          6          2
>>7          6          9
>>2          1          8
>>3          2          8
>>

>>
>>Thank you.
>
>
>I ran the following query in SQL Server it runs. One thing you didn't mention was how do you decide what the most recent record is?
>
>The following SQL SELECT statement (running in SQL Server) should produce the correct results, except for the missing first record. You could do a Union with this query and selecting the first record. Field1a, field2a, field3a have the correct values required for any record where there is a null in that position.
>
>Also attached is the actual query result, using your table and an additional column called nrecno
>
>select a.nrecno, a.field1, a.field2, a.field3, final.field1a, final.field2a, final.field3a FROM
>(
> SELECT f1.nrecno, field1a, field2a, field3a
> FROM
> (
> SELECT f1a.nrecno, field1a = bb1.field1
> FROM
> (
> SELECT b1b.nrecno, maxnrecno = max(b1a.nrecno)
> FROM test b1a , test b1b
> WHERE b1a.field1 IS NOT NULL and b1a.nrecno < b1b.nrecno
> group by b1b.nrecno
> )
> as f1a, test bb1
> WHERE f1a.maxnrecno = bb1.nrecno
> )
> as f1,
>
> (
> SELECT f2a.nrecno, field2a = bb2.field2
> FROM
> (
> SELECT b2b.nrecno, maxnrecno = max(b2a.nrecno)
> FROM test b2a , test b2b
> WHERE b2a.field2 IS NOT NULL and b2a.nrecno < b2b.nrecno
> group by b2b.nrecno
> )
> as f2a, test bb2
> WHERE f2a.maxnrecno = bb2.nrecno
> )
> as f2,
>
> (
> SELECT f3a.nrecno, field3a = bb3.field3
> FROM
> (
> SELECT b3b.nrecno, maxnrecno = max(b3a.nrecno)
> FROM test b3a , test b3b
> WHERE b3a.field3 IS NOT NULL and b3a.nrecno < b3b.nrecno
> group by b3b.nrecno
> )
> as f3a, test bb3
> WHERE f3a.maxnrecno = bb3.nrecno
> )
> as f3
> where f1.nrecno = f2.nrecno and f3.nrecno = f2.nrecno
>
>)
> as final, test a
>where final.nrecno = a. nrecno
>
>Replace the null value with the value in the 'a' field.
>
>nrecno field1 field2 field3 field1a field2a field3a
>----------- ----------- ----------- ----------- ----------- ----------- -----------
>2 5 6 (null) 3 5 6
>3 7 (null) 2 5 6 6
>4 (null) (null) 9 7 6 2
>5 2 1 8 7 6 9
>6 3 2 (null) 2 1 8
>
>(5 row(s) affected)
>
>you will see that the results are what you want, except for the missing first record.