Exclude group of records—if number ever goes up

Solution 1:

Here's one option:

• find "next" condition_rating value (within the same road_id - that's the partition by clause, sorted by insp_date)
• return road_id whose difference between the "next" and "current" condition_rating is less than zero

SQL> with temp as
  2    (select road_id,
  3            condition_rating,
  4            nvl(lead(condition_rating) over (partition by road_id order by insp_date),
  5                condition_rating) next_cr
  6     from test
  7    )
  8  select distinct road_id
  9  from temp
 10  where condition_rating - next_cr < 0;

   ROAD_ID
----------
      3180

SQL>

Solution 2:

Based on OPs own answer, which make the expected outcome more clear.

In my permanent urge to avoid self-joins I'd go for the nested window function:

SELECT road_id, condition_rating, insp_date
  FROM ( SELECT prev.*
              , COUNT(CASE WHEN condition_rating < next_cr THEN 1 END) OVER(PARTITION BY road_id) bad
           FROM (select t.*
                      , lead(condition_rating) over (partition by road_id order by insp_date) next_cr
                  from t
                ) prev
       ) tagged
WHERE bad = 0
ORDER BY road_id, insp_date

NOTE

• lead() gives null for the last row which the query considers by the case expression to mark bad rows: condition_rating < next_cr — if next_cr is null, the condition won't be true so that the case maps it as "not bad".
• The case is just to mimic the filter clause: https://modern-sql.com/feature/filter
• MATCH_RECOGNIZE might be another option to this problem, but due to the lack of '^' and '$' I'm worried that the backtracking might cause more problems it is worth.
• Nested window functions are typically no big performance hit if they use compatible OVER clauses, like in this query.