CODEWITHSUNDEEP
sqlsql-serverlaglead
MrMuffin
MrMuffin

Reputation: 53

How to get the next next to last value if previous value is null

I am trying to clean up a table that basically fill up all the empty value from the 1st non-null value.

The sample table:

ID Number Type
1  51280  %
1         A
2  51279  %
2         B
3  50631  %
3         A
3         B
3         C

There is always a number populated for type '%' and if there is other types the record is null. I need to fill up all the empty rows within type '%'.

The final table should be like this:

ID Number Type
1  51280  %
1  51280  A
2  51279  %
2  51279  B
3  50631  %
3  50631  A
3  50631  B
3  50631  C

I tried using lag function in sql server. 

select ID, number, type,
  case when number is not null then number 
    else lag(number) over (order by id) end as new_number
from tbl
order by ID;

It works fine for records only has 1 Type besides '%' Type. For records that have multiple types, such as Id 3 it will only has 1 record filled up. I know that since lag() only takes the previous value so Type 'B' for ID 3 will only take Number value from Type 'A', while the value for Type 'A' is null.

Attaching an example result from my code.


Number  Type    New_number ID 
50201   %       50201      22
NULL    COMP    50201      22
50668   %       50668      22
NULL    COMP    50668      22
50617   %       50617      22
NULL    COMP    50617      22
196794  %       196794     22
NULL    COMP    196794     22
1       %       1          22
NULL    XO      1          22
NULL    COMP    NULL       22

As you can see the last record is null but it should be 1 instead.

I also tried using Max() w/o case when condition but the result only takes in the largest number in that particular id. 

Number  Type    new_number  ID
50201   %       51827       22
NULL    COMP    51827       22
50668   %       51827       22
NULL    COMP    51827       22
50617   %       51827       22
NULL    COMP    51827       22
196794  %       51827       22
NULL    COMP    51827       22
1       %       51827       22
NULL    XO      51827       22
NULL    COMP    51827       22

Is there a way to skip all the null values and only take the top 1 value by Type '%' group?

Upvotes: 3

Views: 1932

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You don't need lag(). Just use max():

select ID, number, type,
       max(number) over (partition by id) as new_number
from tbl
order by ID;

Only one value is populated, so you don't even need the NOT NULL comparison.

Upvotes: 3

Related Questions