带条件过滤器的Oracle查询

我建议这样做;阅读代码中的注释

SQL> with test (record_id, title, status, activity_date) as
  2    -- sample data
  3    (select 111, 'test1', 'active'  , date '2020-10-11' from dual union all
  4     select 112, 'test1', 'inactive', date '2020-10-11' from dual union all
  5     select 113, 'test2', 'inactive', date '2021-09-26' from dual union all
  6     select 114, 'test2', 'inactive', date '2021-04-14' from dual
  7    ),
  8  temp as
  9    -- rank them - first by status, then by date in descending order
 10    (select t.*,
 11       rank() over (partition by title order by status, activity_date desc) rnk
 12     from test t
 13    )
 14  -- finally, return rows that rank the "highest"
 15  select m.record_id, m.title, m.status, m.activity_date
 16  from temp m
 17  where m.rnk = 1;

 RECORD_ID TITLE STATUS   ACTIVITY_D
---------- ----- -------- ----------
       111 test1 active   10/11/2020
       113 test2 inactive 09/26/2021

SQL>