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欢迎指导

2个回答

    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
    where px <= 5 order by ORDERED DESC, cdate desc
rp1qfd
rp1qfd 以日期为导向的,先日期,后组,再5条
5 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐