Oralce分组排序

场景：需要查询根据某些字段进行分组，同时进行组内排序的取前几条或者最新一条数据。

原始数据：

select
    mid,
    deptname,
    changecol,
    unchangecol,
    bdate
from super_test2

获取根据mid和deptname进行分组的最新一条数据：

with data_sort as (
select 
     t.*,
     row_number() over(partition by mid, deptname order by bdate desc) as rn
from super_test2 t
)

select *
from data_sort
where  rn< 2