Oracle – Select Where Next Row’s Value is Greater

oracleoracle-12cselecttime

I have a road_insp table.

  create table road_insp
   (    
    insp_id int,
    road_id int,
    insp_date date, 
    condition number(38,2)
   ) ;

insert into road_insp (insp_id, road_id, insp_date, condition) values (1,1,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (2,1,to_date('01-JAN-04','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (3,1,to_date('01-JAN-07','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (4,1,to_date('01-JAN-10','DD-MON-YY'),06);

insert into road_insp (insp_id, road_id, insp_date, condition) values (5,2,to_date('01-JAN-02','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (6,2,to_date('01-JAN-05','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (7,4,to_date('01-JAN-03','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (8,4,to_date('01-JAN-06','DD-MON-YY'),12);
insert into road_insp (insp_id, road_id, insp_date, condition) values (9,4,to_date('01-JAN-09','DD-MON-YY'),08);

insert into road_insp (insp_id, road_id, insp_date, condition) values (10,5,to_date('01-JAN-01','DD-MON-YY'),10);
insert into road_insp (insp_id, road_id, insp_date, condition) values (11,5,to_date('01-JAN-03','DD-MON-YY'),09);
insert into road_insp (insp_id, road_id, insp_date, condition) values (12,5,to_date('01-JAN-06','DD-MON-YY'),08);
insert into road_insp (insp_id, road_id, insp_date, condition) values (13,5,to_date('01-JAN-09','DD-MON-YY'),07);
insert into road_insp (insp_id, road_id, insp_date, condition) values (14,5,to_date('01-JAN-12','DD-MON-YY'),06);
insert into road_insp (insp_id, road_id, insp_date, condition) values (15,5,to_date('01-JAN-15','DD-MON-YY'),05);
insert into road_insp (insp_id, road_id, insp_date, condition) values (16,5,to_date('01-JAN-18','DD-MON-YY'),20);

select
    *
from
    road_insp
order by
    road_id,
    insp_date



   INSP_ID    ROAD_ID   INSP_DATE  CONDITION
---------- ----------   --------- ----------
         1          1   01-JAN-01         10
         2          1   01-JAN-04          9
         3          1   01-JAN-07          8
         4          1   01-JAN-10          6

         5          2   01-JAN-02         10
         6          2   01-JAN-05          8

         7          4   01-JAN-03         10
         8          4   01-JAN-06         12 <-error
         9          4   01-JAN-09          8

        10          5   01-JAN-01         10
        11          5   01-JAN-03          9
        12          5   01-JAN-06          8
        13          5   01-JAN-09          7
        14          5   01-JAN-12          6
        15          5   01-JAN-15          5
        16          5   01-JAN-18         20 <-error

For each road, I want to know if the condition ever goes UP over time. The idea being, roads don't just magically improve in condition.

If the condition of the road ever goes up, then the culprit inspection should be flagged.

How can I analyze this table to find anomalies (conditions that go up)?

Example:

      INSP_ID   ROAD_ID     COND_ERROR_DATE  COND_ERROR
    ---------- ----------   ---------------- ----------
            8           4   01-JAN-06                12
           16           5   01-JAN-18                20

Best Answer

select
  insp_id, road_id, insp_date as cond_error_date, condition as cond_error 
from 
(
  select
    insp_id, road_id, insp_date, condition, 
    lag(condition) over (partition by road_id order by insp_date) as condition_before
  from
    road_insp
)
where condition > condition_before;

   INSP_ID    ROAD_ID COND_ERRO COND_ERROR
---------- ---------- --------- ----------
         8          4 01-JAN-06         12
        16          5 01-JAN-18         20
Related Question