Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

version
PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

with test (id, agreement_group_id,  active,  field1,  field2, status_action_date ) as                                               
     (values  (1,55, 'N',45,50, to_timestamp('01-Oct-202100:03:05','dd-Mon-yyyyhh24:mi:ss'))                                         
            , (2,55, 'Y',78,70, to_timestamp('30-Sep-202103:04:05','dd-Mon-yyyyhh24:mi:ss'))                                         
            , (3,100,'N',44, 4, to_timestamp('02-Oct-202104:00:01','dd-Mon-yyyyhh24:mi:ss'))                                          
            , (4,101,'N', 1, 2, to_timestamp('02-Oct-202104:01:05','dd-Mon-yyyyhh24:mi:ss'))                                           
            , (5,101,'N', 0, 1, to_timestamp('05-Oct-202111:13:54','dd-Mon-yyyyhh24:mi:ss'))                                           
            , (6,102,'Y', 3, 3, to_timestamp('05-Oct-202111:47:45','dd-Mon-yyyyhh24:mi:ss'))                                           
            , (7,102,'N', 4, 4, to_timestamp('05-Oct-202111:48:49','dd-Mon-yyyyhh24:mi:ss'))                                           
      )                                                                                                                             
select distinct on (agreement_group_id)                                                                                             
       agreement_group_id, id                                                                                                                           
  from test                                                                                                                         
 order by agreement_group_id                                                                                                        
        , case when active = 'Y' then 0 else 1 end                                                                                  
        , status_action_date desc;

agreement_group_id	id
55	2
100	3
101	5
102	6