Select most recent inspection, break tie between inspections from same year

I have a road_insp table:

create table road_insp
(
    insp_id int,
    road_id int,
    insp_date date,
    length number(10,2)
);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 1, 100, to_date('01/JAN/2013 6:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 2, 100, to_date('01/JAN/2014 7:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 3, 100, to_date('01/JAN/2015 8:00:00AM','DD/MON/YY HH:MI:SSAM'), 1000);

insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 4, 100, to_date('01/JAN/2016 9:00:00AM','DD/MON/YY HH:MI:SSAM'), 750);
insert into user.road_insp (insp_id, road_id, insp_date, length) values ( 5, 100, to_date('01/FEB/2016 10:00:00AM','DD/MON/YY HH:MI:SSAM'), 250);

commit;

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from road_insp;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         1        100 01/JAN/13 06:00:00AM       1000
         2        100 01/JAN/14 07:00:00AM       1000
         3        100 01/JAN/15 08:00:00AM       1000

         4        100 01/JAN/16 09:00:00AM        750
         5        100 01/FEB/16 10:00:00AM        250

I'm able to select the most recent inspection of each road_id using this query:

select
    insp_id,
    road_id,
    to_char(insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
    length
from 
(
      select
        r.insp_id
        ,r.road_id
        ,r.insp_date
        ,r.length
        ,row_number() over (partition by road_id order by insp_date desc) rn
    from user.road_insp r
) 
where
     rn =1
;

   INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
---------- ---------- -------------------- ----------
         5        100 01/FEB/16 10:00:00AM        250

However, rather th

You can modify the order by clause in the window function:

row_number() over 
    (partition by road_id 
     order by extract(year from insp_date) desc,   -- latest year
              length desc                          -- higher length
    ) as rn

Tested in dbfiddle.uk (only in version 11g).

In version 12c, you could also use the FETCH FIRST syntax.

The wanted PARTITION BY makes things more difficult than a simple FETCH though. We'll have to use a LATERAL inline view or CROSS APPLY:

select
    r.*
from
    ( select distinct road_id
      from road_insp
    ) d,
  lateral
    ( select
          ri.insp_id,
          ri.road_id,
          to_char(ri.insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
          ri.length
      from road_insp ri
      where ri.road_id = d.road_id
      order by extract(year from ri.insp_date) desc,
               ri.length desc
      fetch first 1 rows only
    ) r 
;

StackExchange Database Administrators Q#188585, answer score: 2