oracle-ORACLE怎麼寫先排序後分組的前5位

ORACLE怎麼寫先排序後分組的前5位

select *
from (select a.*,
row_number() over(partition by ordered order by cdate desc) px
from (select a.*, b.user_name username
from view_hjzx a, gjjx.sys_users b
where a.userid = b.userid(+)
and a.HANDSET like '%15901054535%') a)
where px <= 5 order by cdate desc
數據是好幾種分類，標識是ORDERED，然後我想達到的目的是，最大的日期的ORDERED作為一組，顯示前五條，然後按日期排序，第二大的ORDERED前五條，第三大的前五條，日期帶領5條組號，不知道怎麼寫，目前的寫法有個問題就是後排序，假設第二組有個日期大於第一組他會跑上去，很苦惱
604790441歡迎指導

最佳回答：

    select *
    from (select a.*,
    row_number() over(partition by ordered order by cdate desc) px,
    FIRST_VALUE(cdate) OVER (partition by ordered order by cdate desc
                             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                            ) ordered_date
    from (select a.*, b.user_name username
    from view_hjzx a, gjjx.sys_users b
    where a.userid = b.userid(+)
    and a.HANDSET like '%15901054535%') a)
    where px <= 5
    order by ordered_date DESC, ORDERED DESC, cdate desc