By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
declare @LedgerEntries as Table ( Entry_No Int, Closed_By_Entry_No Int, Group_Id Int );
insert into @LedgerEntries ( Entry_No, Closed_By_Entry_No, Group_Id ) values
( 1, 4, 1 ),
( 2, 4, 1 ),
( 3, 4, 1 ),
( 4, 5, 1 ),
( 5, 0, 1 ),
( 6, 9, 2 ),
( 7, 9, 2 ),
( 8, 9, 2 ),
( 9, 11, 2 ),
( 10, 11, 2 ),
( 11, 0, 2 ),
( 12, 14, 3 ),
( 13, 14, 3 ),
( 14, 0, 3 ),
( 15, 16, 4 ),
( 16, 0, 4 ),
( 17, 0, 5 ),
( 18, 20, 6 ),
( 19, 20, 6 ),
( 20, 22, 6 ),
( 21, 22, 6 ),
( 22, 0, 6 );
select * from @LedgerEntries;
with Hierarchy as (
-- Anchor query.
select Entry_No, Closed_By_Entry_No, Group_Id,
Row_Number( ) over ( order by Entry_No ) as NewGroupId
from @LedgerEntries
where Closed_By_Entry_No = 0
union all
-- Recursive query.
select LE.Entry_No, LE.Closed_By_Entry_No, LE.Group_Id, H.NewGroupId
| Entry_No | Closed_By_Entry_No | Group_Id |
|---|---|---|
| 1 | 4 | 1 |
| 2 | 4 | 1 |
| 3 | 4 | 1 |
| 4 | 5 | 1 |
| 5 | 0 | 1 |
| 6 | 9 | 2 |
| 7 | 9 | 2 |
| 8 | 9 | 2 |
| 9 | 11 | 2 |
| 10 | 11 | 2 |
| 11 | 0 | 2 |
| 12 | 14 | 3 |
| 13 | 14 | 3 |
| 14 | 0 | 3 |
| 15 | 16 | 4 |
| 16 | 0 | 4 |
| 17 | 0 | 5 |
| 18 | 20 | 6 |
| 19 | 20 | 6 |
| 20 | 22 | 6 |
| 21 | 22 | 6 |
| 22 | 0 | 6 |
| Entry_No | Closed_By_Entry_No | Group_Id | NewGroupId |
|---|---|---|---|
| 1 | 4 | 1 | 1 |
| 2 | 4 | 1 | 1 |
| 3 | 4 | 1 | 1 |
| 4 | 5 | 1 | 1 |
| 5 | 0 | 1 | 1 |
| 6 | 9 | 2 | 2 |
| 7 | 9 | 2 | 2 |
| 8 | 9 | 2 | 2 |
| 9 | 11 | 2 | 2 |
| 10 | 11 | 2 | 2 |
| 11 | 0 | 2 | 2 |
| 12 | 14 | 3 | 3 |
| 13 | 14 | 3 | 3 |
| 14 | 0 | 3 | 3 |
| 15 | 16 | 4 | 4 |
| 16 | 0 | 4 | 4 |
| 17 | 0 | 5 | 5 |
| 18 | 20 | 6 | 6 |
| 19 | 20 | 6 | 6 |
| 20 | 22 | 6 | 6 |
| 21 | 22 | 6 | 6 |
| 22 | 0 | 6 | 6 |