>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.