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][1] 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 than simply just select the most recent inspection, I want to do do something a bit more complex:

 1. Get the most recent inspection
 2. But if there is more than one inspection in that inspection year, break the tie by getting the longest inspection

-----




       INSP_ID    ROAD_ID INSP_DATE_FORMATTED      LENGTH
    ---------- ---------- -------------------- ----------
             4        100 01/JAN/16 09:00:00AM        750


In other words, if there are multiple inspections in the latest inspection year, it doesn't really matter which one came first (within the context of the year). It's more important which inspection was longest.

How can I do this?

  [1]: https://dba.stackexchange.com/questions/165782/select-most-recent-inspection