Get the highest parent id recursively in MySQL
WITH RECURSIVE 
cte as ( SELECT id, id nextid, parentid 
         FROM t1
       UNION ALL
         SELECT cte.id, t1.id, t1.parentid
         FROM t1
         JOIN cte ON cte.parentid = t1.id )
SELECT Id, nextid RootId
FROM cte
WHERE parentid IS NULL

https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=209df940143d3e984d418e49929bd847