How to Replace NULL Values with the Previous Non-NULL Value in SQL?

Replace NULL Values with Previous Known Value in SQL

In SQL databases, it is common to encounter tables with missing or NULL values. In certain scenarios, it is necessary to replace these NULL values with the most recent known value from a previous row. This is particularly useful when working with time-series data, where missing values can disrupt data analysis.

Example:

Consider the following table representing a time series of numbers indexed by date:

date   number
----   ------
1      3
2      NULL
3      5
4      NULL
5      NULL
6      2

We want to replace the NULL values in the number column with the most recent known value from the previous row. For example, date 2 should be filled with 3, dates 4 and 5 should be filled with 5, and so on.

Solution Using SQL Server:

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2


SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

This query uses a subquery to find the most recent non-NULL value for each row with a NULL value. The ISNULL function then replaces the NULL values with the selected value. The result will be a table with the NULL values replaced by the previous known value.

The above is the detailed content of How to Replace NULL Values with the Previous Non-NULL Value in SQL?. For more information, please follow other related articles on the PHP Chinese website!