Get the highest parent id recursively in MySQL

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Method 2

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply