SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table Person (id int, name varchar(50), CentreId int);

insert into Person select 1, 'Sara', 1;
insert into Person select 2, 'Ali', 1;
insert into Person select 3, 'Susan', 1;
insert into Person select 4, 'Jack', 2;
insert into Person select 5, 'Tom', 2;

5 rows affected

alter table Person add Seq int;
with u as (
  Select *,
  row_number() over (partition by CentreId order by Id) as rn
  from Person
)
update u
set seq=rn;
5 rows affected

select * from person


id
name
CentreId
Seq


1
Sara
1
1

2
Ali
1
2

3
Susan
1
3

4
Jack
2
1

5
Tom
2
2