MySQL 8.0

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

create table testt 
(
ID INT,
VALST VARCHAR(255),
VALEND VARCHAR(255),
NEXTID INT
);
INSERT INTO testt (ID, VALST, VALEND, NEXTID)
SELECT    1,   'MOS'  ,   'SAN'  ,    2 FROM DUAL UNION ALL
SELECT    2,   'SAN'  ,   'MOS'  , NULL FROM DUAL UNION ALL
SELECT    3,   'MOS'  ,   'EKB'  ,    4 FROM DUAL UNION ALL
SELECT    4,   'EKB'  ,   'RYA'  ,    5 FROM DUAL UNION ALL
SELECT    5,   'RYA'  ,   'SAN'  ,    6 FROM DUAL UNION ALL
SELECT    6,   'SAN'  ,   'MOS'  , NULL FROM DUAL UNION ALL
SELECT    7,   'MOS'  ,   'KRA'  ,    8 FROM DUAL UNION ALL
SELECT    8,   'KRA'  ,   'VOR'  ,    9 FROM DUAL UNION ALL
SELECT    9,   'VOR'  ,   'MOS'  , NULL ;
SELECT * FROM testt;


ID
VALST
VALEND
NEXTID


1
MOS
SAN
2

2
SAN
MOS
null

3
MOS
EKB
4

4
EKB
RYA
5

5
RYA
SAN
6

6
SAN
MOS
null

7
MOS
KRA
8

8
KRA
VOR
9

9
VOR
MOS
null


WITH RECURSIVE
cte1 AS ( SELECT ID, VALST, VALEND, NEXTID, 1 AS length, ROW_NUMBER() OVER (ORDER BY ID) chain
          FROM testt
          WHERE NEXTID IS NULL
          UNION ALL
          SELECT testt.ID, testt.VALST, cte1.VALEND, testt.NEXTID, cte1.length + 1, cte1.chain
          FROM testt
          JOIN cte1 ON testt.NEXTID = cte1.ID ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY chain ORDER BY length DESC) rn
          FROM cte1 )
SELECT ID, VALST, length 
FROM cte2
WHERE rn = 1;


ID
VALST
length


1
MOS
2

3
MOS
4

7
MOS
3

ID	VALST	VALEND	NEXTID
1	MOS	SAN	2
2	SAN	MOS	null
3	MOS	EKB	4
4	EKB	RYA	5
5	RYA	SAN	6
6	SAN	MOS	null
7	MOS	KRA	8
8	KRA	VOR	9
9	VOR	MOS	null