3

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 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?

2

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 
;
  • @Wilson, MTO is right, if you want partition by something, fetch first is not going to help. I'll edit my answer to clarify. – ypercubeᵀᴹ Oct 26 '17 at 2:21
  • @Wilson, I don't know what I was thinking when I added the fetch first query. It has been edited now - and hopefully corrected. Unfortunately dbfiddle is on version 11, so I can't test it. – ypercubeᵀᴹ Oct 26 '17 at 2:43
  • @Wilson Yeah but it doesn't look like much of an improvement over the row_number version ;) – ypercubeᵀᴹ Oct 26 '17 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.