flap13 flap13 - 6 months ago 13
SQL Question

Oracle, skip duplicate rows on specific key by sort

How I can skip the duplicate record when list sorted, for example,

I have table:


EmpID Date Dept OtherField
1 2017.02.03 11 1
1 2016.02.03 11 2
1 2015.02.03 13 7
1 2014.02.03 21 6
1 2013.02.03 21 12
1 2012.02.03 13 333


I need get:

1 2016.02.03 11
1 2015.02.03 13
1 2013.02.03 21
1 2012.02.03 13

Answer

Thanks for the clarification. Tabibitosan would suit your needs, I believe:

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