閉包テーブル (Closure Table) に順序を持たせソートした結果を取得する
MariaDB のバージョンです。
GROUP_CONCATはデフォルトで1,024文字なので group_concat_max_len を拡張します。
SET SESSION group_concat_max_len = 1000000;
SELECT
s.id
, s.description
, t.path
FROM
(
SELECT
t1.descendant
, GROUP_CONCAT(
LPAD(ss.nth_child, 2, '0')
ORDER BY
t2.path_length DESC SEPARATOR '-'
) path
FROM
tree_paths AS t1
INNER JOIN tree_paths AS t2
ON t2.recipe_id = t1.recipe_id
AND t2.descendant = t1.descendant
INNER JOIN steps AS ss
ON ss.recipe_id = t2.recipe_id
AND ss.id = t2.ancestor
WHERE
t1.recipe_id = 1
AND t1.ancestor = 1
GROUP BY
t1.descendant
) AS t
INNER JOIN steps AS s
ON s.recipe_id = 1
AND s.id = t.descendant
ORDER BY
path ASC