Home > Database > Mysql Tutorial > How to Replace NULL Values with the Previous Non-NULL Value in SQL?

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

Barbara Streisand
Release: 2025-01-04 10:42:34
Original
933 people have browsed it

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
Copy after login

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
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template