我正在更新SQL Developer中以前由其他人生成的现有报告。看起来，脚本的某个部分的排序没有按预期运行。
脚本的以下部分似乎工作正常（最初是CTE，为了本文的目的略有修改）：

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

它提供以下输出：output with correct row ordering
然而，当查看脚本的下一部分时（另一个CTE已为本文进行了修改），行编号并没有按预期应用：

, 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

incorrect ordering based on PATHWAY_STEP_START_DATE