你需要一个 left join :

select t.*
from t left join
     t tp
     on tp.id = t.parent_id
order by coalesce(tp.parent_id, t.parent_id, t.id),
         coalesce(t.parent_id, t.id),
         t.id

这假设父id小于id，这对于您的数据似乎是正确的。这不是一个必要的假设，但它简化了逻辑。

用一个 LEFT 自联接和条件排序：

select t1.*
from tablename t1
left join tablename t2 on t2.id = t1.parent_id
order by coalesce(t2.parent_id, t1.parent_id, t1.id),
         case when t1.parent_id is null then 0 else 1 end,
         case when t2.parent_id is not null then t1.parent_id else t1.id end,
         case when t2.parent_id is null then 0 else 1 end,
         t1.id

请看演示。
结果：

>     ID | PARENT_ID | LEVEL    
> -----: | --------: | :--------
>  29101 |      null | LVL_ONE  
> 153799 |     29101 | LVL_TWO  
> 153800 |    153799 | LVL_THREE
> 153801 |    153799 | LVL_THREE
> 153803 |     29101 | LVL_TWO  
> 153804 |    153803 | LVL_THREE
> 153802 |      null | LVL_ONE  
> 153805 |    153802 | LVL_TWO  
> 153806 |    153805 | LVL_THREE

如果您使用的视图是层次查询，那么您可以选择“order siblees by”，我建议您使用它
下面是一个例子https://oracle-base.com/articles/misc/hierarchical-queries

drop table a1;

CREATE TABLE a1
as 
SELECT 153804 as ID, 153803 as PARENT_ID
FROM DUAL
UNION ALL
SELECT 153801, 153799
FROM DUAL
UNION ALL
SELECT 153803, 29101
FROM DUAL
UNION ALL
SELECT 29101, NULL
FROM DUAL
UNION ALL
SELECT 153802, NULL
FROM DUAL
UNION ALL
SELECT 153805, 153802
FROM DUAL
UNION ALL
SELECT 153806, 153805
FROM DUAL
UNION ALL
SELECT 153800, 153799
FROM DUAL
UNION ALL
SELECT 153799, 29101
FROM DUAL
;

select id, parent_id, level, 'LEVEL_' || level as level_desc
from a1
start with parent_id is null
connect by parent_id = prior(id)
order siblings by id
;