SQL Developer中报表行号排序不符合预期问题求助

各位好，我正在SQL Developer里维护一份同事之前写的报表，现在碰到了一个排序相关的问题，想请教下大家。

首先，我有一段查询（原本是CTE，我做了简化方便提问），这段查询里的行号pp_order_first是正常按wsp.started_on排序生成的，输出结果的顺序也没问题：

select p.person_id person_id
, p.full_name name
, date_of_birth date_of_birth
/*        , p.gender gender
, (select e.ethnicity_category || case when e.sub_ethnicity_category is not null then ' - ' || e.sub_ethnicity_category end from dm_ethnicities e where e.ethnicity_code = p.full_ethnicity_code) ethnicity
, case when exists (select 1 from disability d where d.person_id = p.person_id) then 'Yes' else 'No' end disability
*/        , ppc.category
, wsp.workflow_id workflow_id
, wsp.workflow_step_id pathway_plan_step_id
, wst.description step_description
, to_char(wsp.started_on, 'DD/MM/YYYY') pathway_step_start_date
, to_char(wsp.completed_on, 'DD/MM/YYYY') pathway_step_end_date
, wsp.step_status pathway_step_status
, fa.date_pathway
, fa.date_of_next_pathway
, round(((params.snapshot_date - nvl(fa.date_pathway, wsp.started_on))/(365.25/12)),2) months_since_plan
/*        , (select listagg(wnat.description, ', ') within group (order by wnat.description)
from dm_workflow_links wl
inner join dm_workflow_nxt_action_types wnat on wnat.workflow_next_action_type_id=wl.workflow_next_action_type_id
inner join dm_workflow_steps ws on ws.workflow_step_id = wl.target_step_id
where wl.source_step_id=wsp.workflow_step_id and ws.step_status <> 'CANCELLED') next_actions_of_pathway
*/        , row_number() over (partition by p.person_id order by wsp.started_on) pp_order_first
from dm_persons p
inner join params on 1=1
inner join pp_cohort ppc
on ppc.person_id=p.person_id
left join dm_workflow_steps_people_vw wsp
on wsp.person_id=p.person_id and wsp.workflow_step_type_id in (75,193) and wsp.cancelled_on is null  --Develop Pathway Plan, Review Pathway Plan
left join dm_workflow_step_types wst
on wst.workflow_step_type_id=wsp.workflow_step_type_id
left join form_answers fa
on fa.workflow_step_id=wsp.workflow_step_id
where add_months(p.date_of_birth, 16*12) <= params.snapshot_date --16+
and wsp.step_status = 'COMPLETED'
and p.person_id = 512937

这段查询的输出是符合预期的，行号和排序都没问题。

但是当我把这段逻辑封装到CTE里，再在外层查询中尝试按pathway_step_start_date倒序生成行号pp_order_latest时，结果就不对了，行号没有按照预期的排序规则分配：

, pathway as
(
select p.person_id person_id
, p.full_name name
, date_of_birth date_of_birth
/*        , p.gender gender
, (select e.ethnicity_category || case when e.sub_ethnicity_category is not null then ' - ' || e.sub_ethnicity_category end from dm_ethnicities e where e.ethnicity_code = p.full_ethnicity_code) ethnicity
, case when exists (select 1 from disability d where d.person_id = p.person_id) then 'Yes' else 'No' end disability
*/        , ppc.category
, wsp.workflow_id workflow_id
, wsp.workflow_step_id pathway_plan_step_id
, wst.description step_description
, to_char(wsp.started_on, 'DD/MM/YYYY') pathway_step_start_date
, to_char(wsp.completed_on, 'DD/MM/YYYY') pathway_step_end_date
, wsp.step_status pathway_step_status
, fa.date_pathway
, fa.date_of_next_pathway
, round(((params.snapshot_date - nvl(fa.date_pathway, wsp.started_on))/(365.25/12)),2) months_since_plan
/*        , (select listagg(wnat.description, ', ') within group (order by wnat.description)
from dm_workflow_links wl
inner join dm_workflow_nxt_action_types wnat on wnat.workflow_next_action_type_id=wl.workflow_next_action_type_id
inner join dm_workflow_steps ws on ws.workflow_step_id = wl.target_step_id
where wl.source_step_id=wsp.workflow_step_id and ws.step_status <> 'CANCELLED') next_actions_of_pathway
*/        , row_number() over (partition by p.person_id order by wsp.started_on) pp_order_first
from dm_persons p
inner join params on 1=1
inner join pp_cohort ppc
on ppc.person_id=p.person_id
left join dm_workflow_steps_people_vw wsp
on wsp.person_id=p.person_id and wsp.workflow_step_type_id in (75,193) and wsp.cancelled_on is null  --Develop Pathway Plan, Review Pathway Plan
left join dm_workflow_step_types wst
on wst.workflow_step_type_id=wsp.workflow_step_type_id
left join form_answers fa
on fa.workflow_step_id=wsp.workflow_step_id
where add_months(p.date_of_birth, 16*12) <= params.snapshot_date --16+
)

select p.person_id
, p.pathway_plan_step_id
, p.step_description
, p.pathway_step_start_date
, p.date_pathway
, p.date_of_next_pathway
, p.months_since_plan
, row_number() over (partition by p.person_id order by p.pathway_step_start_date desc) pp_order_latest
from pathway p
where p.pathway_step_status='COMPLETED'
and p.person_id = 512937

这段查询的结果里，pp_order_latest的行号并没有按照pathway_step_start_date倒序来正确生成，排序不符合预期。

有没有大佬能帮我看看问题出在哪？

备注：内容来源于stack exchange，提问作者JamesL24