3

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?

Dale K
  • 16,372
  • 12
  • 37
  • 62
MrMuffin
  • 45
  • 1
  • 5
  • 2
    You need some way to order your rows... how do you tell which rows with a given ID come before and after any given row with a `%` type? Remember, tables are inherently unordered sets. – Shawn Aug 06 '19 at 01:18

1 Answers1

3

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.

Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
  • 3
    I actually tried that as well but unfortunately it does not work for my data set. It only returns the max value per ID but the Number needs to be unique by Type. For example the data set has 74 records that share the same ID '22' but each each Type has a unique Number. I need to fill up the Number by Type. – MrMuffin Aug 05 '19 at 23:03
  • 2
    @MrMuffin . . . This works for your sample data and the description that you have. I would suggest that you ask a new question, and be clearer on the rules and sample data. – Gordon Linoff Aug 05 '19 at 23:11
  • 1
    thanks for the suggestion. Just uploaded the sample result for your reference. – MrMuffin Aug 05 '19 at 23:20