New answers tagged

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 | ...


3

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


3

Needed ordering may be performed by the next query: WITH cte AS ( SELECT *, CAST(list_order AS TEXT) as ord FROM items WHERE parent_id = 0 UNION ALL SELECT items.*, cte.ord || '.' || items.list_order FROM items JOIN cte ON cte.id = items.parent_id ) SELECT * FROM cte ORDER BY ord https://dbfiddle.uk/?...


Top 50 recent answers are included