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

Managing Null Values in SQL: Replacing Null Values with Previous Known Values

A common challenge in working with SQL databases is encountering null values, which can represent missing or unknown data. To ensure data integrity and analysis accuracy, it's essential to handle null values appropriately.

Problem Statement:

Consider a table with two columns: date and number, where some rows have null values in the number column. The objective is to replace these null values with values taken from the last known non-null value in the previous date row.

Solution:

For SQL Server users, the following query can be utilized:

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

Explanation:

• Create a temporary table @Table to store the sample data.
• Use the ISNULL() function to check if Val is null. If it is, the TOP 1 subquery is executed to retrieve the last known non-null value for that date.
• The subquery filters the table for rows with an ID less than the current row and non-null Val values, then orders them in descending order by ID to get the most recent value.
• The final result includes all rows with the original or replaced number values.

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