Q: Get the highest parent id recursively in MySQL I want to find the uppermost parent ID for each entry. For example,
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3);

I intent to get
ID     Highest ParentID
1      NULL
2      1
3      1
4      1

I planned to create a recursive query (MySQL 8 or MariaDB 10.5) by adding a condition to break the recursive when Parent ID is a specific value (e.g., NULL in the above case). I started with
WITH RECURSIVE cte (ID, ParentID) as (
  SELECT ID,ParentID FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT * FROM cte;

but it does not work as I intend, as it gets the next ParentID instead of recursively.
Sample fiddle.

A: 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&fiddle=209df940143d3e984d418e49929bd847

A: A possibility from Akinas cte is

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,NULL),(2,1),(3,2),(4,3),(5,NULL),(6,5),(7,6);

SELECT * FROM t1;


✓

✓

ID | ParentID
-: | -------:
 1 |     null
 2 |        1
 3 |        2
 4 |        3
 5 |     null
 6 |        5
 7 |        6



WITH RECURSIVE cte (ID, ParentID, path) as (
  SELECT ID,ParentID, ID path
  FROM t1
  UNION ALL
  SELECT t2.ID,t2.ParentID, cte.path 
  FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT ID,MIN(PATH) FROM cte GROUP BY ID;


ID | MIN(PATH)
-: | --------:
 1 |         1
 2 |         1
 3 |         1
 4 |         1
 5 |         5
 6 |         5
 7 |         5


db<>fiddle here
a  adaption to the first, if the number are not sequentiell, you nned to start with

CREATE TABLE t1
(
ID int(11) unsigned NOT NULL,
ParentID int(11) unsigned,
PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

INSERT INTO t1 (ID,ParentID) VALUES (1,10),(10,20),(20,NULL),(2,1),(3,2),(4,3),(5,NULL),(6,5),(7,6);

SELECT * FROM t1;


✓

✓

ID | ParentID
-: | -------:
 1 |       10
 2 |        1
 3 |        2
 4 |        3
 5 |     null
 6 |        5
 7 |        6
10 |       20
20 |     null



WITH RECURSIVE cte (ID, ParentID, path) as (
  SELECT ID,ParentID, ID path
  FROM t1
  WHERE ParentID IS NULL

  UNION ALL
  SELECT t2.ID,t2.ParentID, cte.path 
  FROM t1 t2
  INNER JOIN cte on t2.ParentID = cte.ID
)
SELECT ID,MIN(PATH) FROM cte GROUP BY ID;


ID | MIN(PATH)
-: | --------:
 5 |         5
20 |        20
 6 |         5
10 |        20
 1 |        20
 7 |         5
 2 |        20
 3 |        20
 4 |        20


db<>fiddle here
