@Query(value = """
            WITH
            cte1 as (
                select distinct
                    woa.id as woa_id
                from
                    work_activity woa
                join activity_assignment waa on waa.activity_id = woa.id
                join activity_assignment_log waal on waal.activity_id = woa.id
                join ref_provider rp on rp.id = waal.provider_id
                where
                    rp.external_id in (:providerIds)
                    and
                    woa.modified_date >= CAST(:modifiedDate AS timestamp)
            )
            select *
            from (
                select wa.id as waId, wa.alternate_id as waAltId, wo.id as woId,
                wo.alternate_id as woAltId, rr.alternate_id as rrAltId,
                wa.description as waDesc, rs.alternate_id as waStatus, wa.eta_start as waEtaStart,
                sl_c.target_date as sle, rot.description as serviceLineDesc,
                (SELECT ro.org_value from ref_organization ro join ref_org_structure ros on ro.structure_id = ros.id where ro.id = any(wo.orgs)
                and ros.structure_name = 'Customer') AS customer,
                (SELECT ro.org_value from ref_organization ro join ref_org_structure ros on ro.structure_id = ros.id where ro.id = any(wo.orgs)
                and ros.structure_name = 'Country') AS country,
                wa.asset_id as assetId, wa.location_id as locationId,
                wa.created_date as createdDate, wa.status_id as statusId,
                rr.id as requestId, sl_c.target_date as targetCompleteDate,
                sl_a.target_date as targetAttendDate, sl_r.target_date as targetRespondDate,
                wa.sequence as sequence,
                (select change_date from activity_status_log asl join ref_status rs on asl.status_id = rs.id
                where activity_id = wa.id and rs.alternate_id = 'IN_PROGRESS'
                order by asl.change_date desc
                limit 1) as actualStartDate,
                rr.priority,
                rp.external_id as providerId,
                ROW_NUMBER() OVER(PARTITION BY aal.activity_id ORDER BY aal.created_date DESC) as rn,
                wa.modified_date as modifiedDate,
                rsc.input_stream_name as sourceChannel
                from work_activity wa
                join work_order wo on wa.work_order_id = wo.id
                join work_assembly wa2 on wo.work_assembly_id = wa2.id
                join ref_request rr on wa2.request_id = rr.id
                join ref_status rs on wa.status_id = rs.id
                join activity_assignment_log aal on wa.id = aal.activity_id
                join activity_assignment aa on aa.activity_id = aal.activity_id and aa.assignee = aal.provider_id
                join ref_provider rp ON aal.provider_id = rp.id
                left join ref_service_classification rsc on rsc.id = rr.service_classification_id
                left join sla_log sl_c on wa.id = sl_c.activity_id and sl_c."name" = 'COMPLETE'
                left join sla_log sl_a on wa.id = sl_a.activity_id and sl_a."name" = 'ATTEND'
                left join sla_log sl_r on wa.id = sl_r.activity_id and sl_r."name" = 'RESPOND'
                left join ref_object_translation rot on wa.service_line_id = rot.object_id
            ) as oa
            JOIN cte1 ON cte1.woa_id = oa.waId
            where ((oa.rn = 1 and oa.providerId in (:providerIds) and oa.sle between now() and now() + interval '3 days')
                or (oa.rn = 1 and oa.providerId in (:providerIds) and oa.targetCompleteDate between now() - interval '3 days' and now())
                or (oa.rn = 1 and oa.providerId in (:providerIds) and oa.waStatus in ('IN_PROGRESS','ON_HOLD'))
                or (oa.rn = 1 and oa.providerId NOT in (:providerIds))
                )
                and oa.modifiedDate >= CAST(:modifiedDate AS timestamp)
        """
        , countQuery = """
            WITH
            cte1 as (
                select distinct
                    woa.id as woa_id
                from
                    work_activity woa
                join activity_assignment waa on waa.activity_id = woa.id
                join activity_assignment_log waal on waal.activity_id = woa.id
                join ref_provider rp on rp.id = waal.provider_id
                where
                    rp.external_id in (:providerIds)
                    and
                    woa.modified_date >= CAST(:modifiedDate AS timestamp)
            )
            select count(*)
            from (
                select wa.id as waId, wa.alternate_id as waAltId, wo.id as woId,
                wo.alternate_id as woAltId, rr.alternate_id as rrAltId,
                wa.description as waDesc, rs.alternate_id as waStatus, wa.eta_start as waEtaStart,
                sl_c.target_date as sle, rot.description as serviceLineDesc,
                (SELECT ro.org_value from ref_organization ro join ref_org_structure ros on ro.structure_id = ros.id where ro.id = any(wo.orgs)
                and ros.structure_name = 'Customer') AS customer,
                (SELECT ro.org_value from ref_organization ro join ref_org_structure ros on ro.structure_id = ros.id where ro.id = any(wo.orgs)
                and ros.structure_name = 'Country') AS country,
                wa.asset_id as assetId, wa.location_id as locationId,
                wa.created_date as createdDate, wa.status_id as statusId,
                rr.id as requestId, sl_c.target_date as targetCompleteDate,
                sl_a.target_date as targetAttendDate, sl_r.target_date as targetRespondDate,
                wa.sequence as sequence,
                (select change_date from activity_status_log asl join ref_status rs on asl.status_id = rs.id
                where activity_id = wa.id and rs.alternate_id = 'IN_PROGRESS'
                order by asl.change_date desc
                limit 1) as actualStartDate,
                rp.external_id as providerId,
                ROW_NUMBER() OVER(PARTITION BY aal.activity_id ORDER BY aal.created_date DESC) as rn,
                wa.modified_date as modifiedDate,
                rsc.input_stream_name as sourceChannel
                from work_activity wa
                join work_order wo on wa.work_order_id = wo.id
                join work_assembly wa2 on wo.work_assembly_id = wa2.id
                join ref_request rr on wa2.request_id = rr.id
                join ref_status rs on wa.status_id = rs.id
                join activity_assignment_log aal on wa.id = aal.activity_id
                join activity_assignment aa on aa.activity_id = aal.activity_id and aa.assignee = aal.provider_id
                join ref_provider rp ON aal.provider_id = rp.id
                left join ref_service_classification rsc on rsc.id = rr.service_classification_id
                left join sla_log sl_c on wa.id = sl_c.activity_id and sl_c."name" = 'COMPLETE'
                left join sla_log sl_a on wa.id = sl_a.activity_id and sl_a."name" = 'ATTEND'
                left join sla_log sl_r on wa.id = sl_r.activity_id and sl_r."name" = 'RESPOND'
                left join ref_object_translation rot on wa.service_line_id = rot.object_id
            ) as oa
            JOIN cte1 ON cte1.woa_id = oa.waId
            where ((oa.rn = 1 and oa.providerId in (:providerIds) and oa.sle between now() and now() + interval '3 days')
                or (oa.rn = 1 and oa.providerId in (:providerIds) and oa.targetCompleteDate between now() - interval '3 days' and now())
                or (oa.rn = 1 and oa.providerId in (:providerIds) and oa.waStatus in ('IN_PROGRESS','ON_HOLD'))
                or (oa.rn = 1 and oa.providerId NOT in (:providerIds))
                )
                and oa.modifiedDate >= CAST(:modifiedDate AS timestamp)
        """, nativeQuery = true)
Page<ActivityViewResult> findAllOfflineActivitiesForProvider(Pageable pageable, @Param("providerIds") List<UUID> providerIds, @Param("modifiedDate") String modifiedDate);

Here in the above query..

the pageable object’s sort by is not getting appended..

it is instead appending , instead of order by

if i remove the order by in Row_Number section, then order by is getting appended correctly..

Any workaround or solution would be greatly appreciated
I have tried a lot but dont have a working query