oracle 排序重复项,Oracle，按排序跳过特定键上的重复行

谢谢你的澄清。 Tabibitosan符合您的需求，我相信：

with sample_data as (select 1 empid, to_date('03/02/2017', 'dd/mm/yyyy') dt, 11 dept, 1 otherfield from dual union all

select 1 empid, to_date('03/02/2016', 'dd/mm/yyyy') dt, 11 dept, 2 otherfield from dual union all

select 1 empid, to_date('03/02/2015', 'dd/mm/yyyy') dt, 13 dept, 7 otherfield from dual union all

select 1 empid, to_date('03/02/2014', 'dd/mm/yyyy') dt, 21 dept, 6 otherfield from dual union all

select 1 empid, to_date('03/02/2013', 'dd/mm/yyyy') dt, 21 dept, 12 otherfield from dual union all

select 1 empid, to_date('03/02/2012', 'dd/mm/yyyy') dt, 13 dept, 333 otherfield from dual)

select empid,

min(dt) dt,

dept

from (select empid,

dt,

dept,

row_number() over (partition by empid order by dt)

- row_number() over (partition by empid, dept order by dt) grp

from sample_data)

group by empid,

dept,

grp

order by empid,

dt desc;

EMPID DT DEPT

---------- ---------- ----------

1 2016.02.03 11

1 2015.02.03 13

1 2013.02.03 21

1 2012.02.03 13