MySQL 8.0

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

 create table rot (
            code int(10) primary key,
            PN varchar(10) not null,
        SN varchar(10) not null,
            LocID int(10) not null);
 insert into rot values (1,'T1','T1SN1','1'),
(2,'A1','A1SN1','2'),
     (3,'J1','J1SN1','3'),
 (4,'A2','A2SN1','1'),
   (5,'J2','J2SN1','2'),
(6,'A3','A3SN1','3'),
(7,'J3','J3SN1','4'),
 (8,'T1','T1SN2','5'),
    (9,'A1','A1SN2','1'),
 (10,'J2','J2SN2','3'),
 (11,'J2','J2SN3','4'),
 (12,'A1','A1SN3','3'),
 (13,'J2','J2SN4','5');
    create table loc(
        code1 int(10) primary key,
        LocVar varchar(10) not null);
insert into loc values (1,'AAA'),(2,'BBB'), (3,'CCC'), (4,'DDD'), (5,'EEE');
SELECT * 
FROM rot
JOIN loc ON rot.code = loc.code1

JOIN (
            SELECT t1.code, count(*) cnt FROM (
                SELECT distinct code
                FROM rot ts1 
            JOIN loc tx1 ON ts1.code = tx1.code1 

) t1 
JOIN (
    SELECT distinct code
                FROM rot ts2
            JOIN loc tx2 ON ts2.code = tx2.code1

    ) t2 on t1.code <= t2.code 
    group by t1.code 

) tt ON rot.code = tt.code


code
PN
SN
LocID
code1
LocVar
code
cnt


1
T1
T1SN1
1
1
AAA
1
5

2
A1
A1SN1
2
2
BBB
2
4

3
J1
J1SN1
3
3
CCC
3
3

4
A2
A2SN1
1
4
DDD
4
2

5
J2
J2SN1
2
5
EEE
5
1


SELECT *, rot.code, ROW_NUMBER() OVER(ORDER BY SN) AS rn
FROM rot
JOIN loc ON rot.code = loc.code1
ORDER BY SN;


code
PN
SN
LocID
code1
LocVar
code
rn


2
A1
A1SN1
2
2
BBB
2
1

4
A2
A2SN1
1
4
DDD
4
2

3
J1
J1SN1
3
3
CCC
3
3

5
J2
J2SN1
2
5
EEE
5
4

1
T1
T1SN1
1
1
AAA
1
5

code	PN	SN	LocID	code1	LocVar	code	cnt
1	T1	T1SN1	1	1	AAA	1	5
2	A1	A1SN1	2	2	BBB	2	4
3	J1	J1SN1	3	3	CCC	3	3
4	A2	A2SN1	1	4	DDD	4	2
5	J2	J2SN1	2	5	EEE	5	1