CODEWITHSUNDEEP
sql-server-2008
Brinda
Brinda

Reputation: 7

Replace null value with previous value without using ID in SQL Server 2008

It's done with the use of ID but I want without using ID 

 SELECT
        ID
        ,COALESCE(p.number, 
            (SELECT TOP (1) number 
            FROM tablea AS p2 
            WHERE
                p2.number IS NOT NULL 
                AND p2.ID <= p.ID ORDER BY p2.ID DESC))as Number--,Result = p.number
    FROM TableA AS p;

ID  number
1   100
2   150
3   NULL
4   300
5   NULL
6   NULL
7   450
8   NULL
9   NULL
10  560
11  NULL
12  880
13  NULL
14  579
15  987
16  NULL
17  NULL
18  NULL

Upvotes: 0

Views: 1372

Answers (1)

Yousuf Sultan
Yousuf Sultan

Reputation: 3257

Try this query. This will help you get your desired result set. This query is written in SQL Server 2008 R2. 

WITH CTE AS
( SELECT id, number FROM tablea)
SELECT A.id, A.number, ISNULL(A.number,B.number) number
FROM CTE A
OUTER APPLY (SELECT TOP 1 *
FROM CTE 
WHERE id < a.id AND number IS NOT NULL
ORDER BY id DESC) B

You can try using LAG and LEAD functions in SQL Server 2012/

Upvotes: 1

Related Questions