SQL Oracle: найдите все записи, доступные с одного и того же «ID», затем выберите только те, которые имеют определенное состояние активности

Вот один вариант.

(Просто задайте формат даты, чтобы узнать, что к чему; из опубликованных вами данных неясно, например, 2023/04/10 может быть 10 апреля, а также 4 октября).

SQL> alter session set nls_date_format = 'yyyy/mm/dd';

Session altered.

Образец данных:

SQL> with test (id, activity, datum) as
  2    (select 1111, 'S', date '2023-04-10' from dual union all
  3     select 2222, 'M', date '2023-04-11' from dual union all
  4     select 3333, 'S', date '2023-04-10' from dual union all
  5     select 1111, 'M', date '2023-04-11' from dual union all
  6     select 3333, 'M', date '2023-04-11' from dual union all
  7     select 4444, 'S', date '2023-04-11' from dual union all
  8     select 5555, 'M', date '2023-04-10' from dual union all
  9     select 5555, 'M', date '2023-04-11' from dual
 10    )

Запрос начинается здесь; temp CTE сортирует все строки (для ID, у которых нет активности S) для каждого ID по datum в порядке убывания):

 11  temp as
 12    (select a.id, a.activity, a.datum,
 13       row_number() over (partition by a.id order by a.datum desc) rn
 14     from test a
 15     where not exists (select null
 16                       from test b
 17                       where b.id = a.id
 18                         and b.activity = 'S'
 19                      )
 20    )

Наконец, верните строки, получившие наивысший рейтинг:

 21  select id, datum
 22  from temp
 23  where rn = 1
 24  order by id;

        ID DATUM
---------- ----------
      2222 2023/04/11
      5555 2023/04/11

SQL>

При условии «М» < «С»

with mx as(
  SELECT tbl.*, MAX(Activity) over (partition by ID) ma, max(Dt) over (partition by ID) mdt
  FROM tbl
) 
select ID, Activity, Dt
from mx
where ma ='M' and dt = mdt

Вы можете использовать аналитические функции, чтобы найти последнюю строку и проверить, было ли действие S ранее, чтобы вы могли запрашивать таблицу только один раз:

SELECT id, activity, dt
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY dt DESC) AS rn,
         FIRST_VALUE(CASE activity WHEN 'S' THEN 'S' END IGNORE NULLS) OVER (
             PARTITION BY id ORDER BY dt DESC
             ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
           ) AS has_activity_s
  FROM   table_name t
)
WHERE rn = 1
AND   has_activity_s IS NULL;

Что для примера данных:

CREATE TABLE table_name (id, activity, dt) AS
  SELECT 1111, 'S', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 2222, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 3333, 'S', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 1111, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 3333, 'M', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 4444, 'S', DATE '2023-04-11' FROM DUAL UNION ALL
  SELECT 5555, 'M', DATE '2023-04-10' FROM DUAL UNION ALL
  SELECT 5555, 'M', DATE '2023-04-11' FROM DUAL;

Выходы:

рабочий пример

Еще один:

select * from data d1
where d1.Activity = 'M' and not exists(
    select 1 from data d2
    where (d1.id = d2.id) and (
        (d2.dat <= d1.dat and d2.Activity = 'S')
        or (d2.dat > d1.dat and d2.Activity = 'M')
    )
)