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;
INSP_ID ROAD_ID INSP_DATE LENGTH
---------- ---------- --------- ----------
1 100 01-JAN-13 1000
2 100 01-JAN-14 1000
3 100 01-JAN-15 1000
4 100 01-JAN-16 750
5 100 01-FEB-16 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, 'mm/dd/yyyy hh24:mi:ss') 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 02/01/2016 10:00:00 250
However, rather than simply just select the most recent inspection, I want to do do something a bit more complex:
- Get the most recent inspection
- 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/01/2016 09:00:00 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 matters more which inspection was longest.
How can I do this?