Continue row number sequence from the largest number

[former_member202516]

Hi experts,

I have a scenario where I need to continue the row number sequence from the last maximum number in the column by partition field.

I think that the following screenshot illustrates the example the best:

The red values do not exist in the source table and need to be derived, is there a window function to be used for this

Thanks

[SergioG_TX · Accepted solution]

MIjodrag,

you have to do this in a few steps... I assume the second column is null

1) i created the original tbl as a local temp table,

2) created #b with similar structure

3) did a row_number function of the null values so i know what is the sequence to add to the final output

4) union the non-null records and add the max value to the seq of non-null records

hope that's what you need.

-- added some initial records

create local temporary table #a ("A" varchar(10), "B" int);

--insert into #a("A","B")

values ('A',null);

create local temporary table #b like #a; -- mimic a temp tbl

-- identify the hightest numbers and save them on a local temp tbl

insert into #b

select "A", max("B") "B"

from #a

group by "A"

select * from #a -- verify original

select * from #b -- verify same structure

select *

from (

      select  y.A, b."B" + y."R" as "B"

      from (

select "A", "B"

, row_number() over (partition by "A" order by "B" DESC) as "R"

from #a

where "B" is null -- select your null rows so you know how many you need to add

      ) y, #b as b

      where y."A" = b."A" -- join them on the common column A

      union

      select * --union the records that already have a value

      from #a

      where "B" is not null

)

order by "A","B" -- reorder the result set

original tbl loos like this..

output looks like this..

[former_member202516 · Accepted solution]

Thanks Sergio, exactly the logic I needed. Actually, later I came up with the similar logic with the row_number on the null values (u were right, they were null initially)

Thanks for your effort.

Best regards,

Mijodrag