Here is a potential solution. It works with the sample data provided.

DECLARE @system_log TABLE(

PK_ID int PRIMARY KEY

,Sequence_ID int null

)

INSERT @system_log(

PK_ID

,Sequence_ID

)VALUES

(1035590, 35587),

(1035589, NULL),

(1035586, NULL),

(1035585, NULL),

(1035584, NULL),

(1035583, 35583),

(1035582, NULL),

(1035581, NULL),

(1035579, NULL),

(1035578, 35553),

(1035554, NULL),

(1035550, 35550);

WITH BaseData as (

SELECT

PK_ID,

Sequence_ID,

rn3 = row_number() over (order by PK_ID) - row_number() over (partition by case when Sequence_ID is null then 0 else 1 end order by PK_ID)

FROM

@system_log

), InertimData as (

select

bd1.PK_ID,

bd1.Sequence_ID,

cn = row_number() over (partition by case when bd1.Sequence_ID is null then 0 else 1 end, bd1.rn3 order by bd1.PK_ID)

from

BaseData bd1

)

select

PK_ID,

coalesce(Sequence_ID, cn) Sequence_ID

from

InertimData

order by

PK_ID desc;