SQL Server 2019

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

CREATE TABLE mytable(
   ID        INTEGER  NOT NULL PRIMARY KEY 
  ,String    VARCHAR(11) NOT NULL
  ,Is_Sep    INT  NOT NULL
  ,Rank      INTEGER  NOT NULL
  ,Rank_DESC INTEGER  NOT NULL
);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (1,'XX',0,1,3);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (2,'XX',0,2,2);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (3,'XX',1,3,1);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (4,'XX',0,1,3);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (5,'XX',0,2,2);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (6,'XX',1,3,1);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (7,'YY',0,1,4);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (8,'YY',0,2,3);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (9,'YY',0,3,2);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (10,'YY',1,4,1);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (11,'ZZ',0,1,2);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (12,'ZZ',1,2,1);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (13,'ZZ',0,1,3);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (14,'ZZ',0,2,2);
INSERT INTO mytable(ID,String,Is_Sep,Rank,Rank_DESC) VALUES (15,'ZZ',1,3,1);
15 rows affected

select 
    id,
    string,
    is_sep,
    row_number() over(partition by string, grp order by id) rn,
    row_number() over(partition by string, grp order by id desc) rn_desc
from (
    select 
        t.*,
        sum(is_sep) over(partition by string order by id desc) grp
    from mytable t
) t
order by id


id
string
is_sep
rn
rn_desc


1
XX
0
1
3

2
XX
0
2
2

3
XX
1
3
1

4
XX
0
1
3

5
XX
0
2
2

6
XX
1
3
1

7
YY
0
1
4

8
YY
0
2
3

9
YY
0
3
2

10
YY
1
4
1

11
ZZ
0
1
2

12
ZZ
1
2
1

13
ZZ
0
1
3

14
ZZ
0
2
2

15
ZZ
1
3
1


select 
    id,
    string,
    is_sep,
    row_number() over(partition by grp order by id) rn,
    row_number() over(partition by grp order by id desc) rn_desc
from (
    select 
        t.*,
        sum(is_sep) over(order by id desc) grp
    from mytable t
) t
order by id


id
string
is_sep
rn
rn_desc


1
XX
0
1
3

2
XX
0
2
2

3
XX
1
3
1

4
XX
0
1
3

5
XX
0
2
2

6
XX
1
3
1

7
YY
0
1
4

8
YY
0
2
3

9
YY
0
3
2

10
YY
1
4
1

11
ZZ
0
1
2

12
ZZ
1
2
1

13
ZZ
0
1
3

14
ZZ
0
2
2

15
ZZ
1
3
1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1

id	string	is_sep	rn	rn_desc
1	XX	0	1	3
2	XX	0	2	2
3	XX	1	3	1
4	XX	0	1	3
5	XX	0	2	2
6	XX	1	3	1
7	YY	0	1	4
8	YY	0	2	3
9	YY	0	3	2
10	YY	1	4	1
11	ZZ	0	1	2
12	ZZ	1	2	1
13	ZZ	0	1	3
14	ZZ	0	2	2
15	ZZ	1	3	1