CODEWITHSUNDEEP
sql-server-2005t-sql
robertc
robertc

Reputation: 75707

Populate column nulls with next non-null value

Is there a straightforward query to fill in the gaps in a column with the next non-null value in that column? I want a column which has data like this:

--------------------
| ID | Version     |
--------------------
|  1 | '162.39'    |
--------------------
|  2 | NULL        |
--------------------
|  3 | NULL        |
--------------------
|  4 | '162.40'    |
--------------------
|  5 | '162.41'    |
--------------------
|  6 | NULL        |
--------------------
|  7 | NULL        |
--------------------
|  8 | '162.42'    |
--------------------
|  9 | '162.42'    |
--------------------
| 10 | NULL        |
--------------------

To end up like this

--------------------
| ID | Version     |
--------------------
|  1 | '162.39'    |
--------------------
|  2 | '162.40'    |
--------------------
|  3 | '162.40'    |
--------------------
|  4 | '162.40'    |
--------------------
|  5 | '162.41'    |
--------------------
|  6 | '162.42'    |
--------------------
|  7 | '162.42'    |
--------------------
|  8 | '162.42'    |
--------------------
|  9 | '162.42'    |
--------------------
| 10 | NULL        |
--------------------

The last value(s) can either be '162.42' or remain at NULL.

Upvotes: 0

Views: 2017

Answers (2)

Mikee
Mikee

Reputation: 646

Try this:

UPDATE table t1 
SET Version = (
                 SELECT TOP 1 Version 
                 FROM table t2 
                 WHERE t2.id > t1.id 
                   AND Version IS NOT NULL 
                 ORDER BY t2.ID ASC
              )
WHERE t1.Version IS NULL

I didnt try it, maybe you will have to fix some details.

Upvotes: 3

starskythehutch
starskythehutch

Reputation: 3338

Yes, you can use a query containing a correlated subquery that will return the top 1 value of Ids below it. Something like the following: (beware code just from memory)

SELECT T1.Id, (SELECT TOP 1 Version FROM MyTable T2 WHERE T2.Id > T1.Id AND Version IS NOT NULL ORDER BY T2.Id ASC)
FROM MyTable T1
WHERE T1.Version IS NULL

You can then use this query to power an update statement to update your table.

Upvotes: 1

Related Questions