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

Question

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

ID is used to define the order of rows. If you have other columns that define the order, use them. If you don't have such a column - add it. Somehow you need to tell the server what does "previous" row mean. — Vladimir Baranov
– Vladimir Baranov, Commented Mar 31, 2016 at 11:59

Yousuf Sultan · Accepted Answer · 2016-04-07 07:10:18Z

1

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/

answered Apr 7, 2016 at 7:10

Yousuf Sultan

3,30711 gold badges40 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related