你发布的代码看起来像Oracle。如果是这样，LAG分析函数可能是一个选择。
样本数据：

SQL> with test (acct_updt_tm, acct_id, acct_eff_dt) as
  2    (select to_date('2023-10-24 23:04:35', 'yyyy-mm-dd hh24:mi:ss'), 56, null              from dual union all
  3     select to_date('2023-10-14 19:34:11', 'yyyy-mm-dd hh24:mi:ss'), 56, date '2020-06-11' from dual union all
  4     select to_date('2023-10-14 19:12:03', 'yyyy-mm-dd hh24:mi:ss'), 56, date '2020-06-10' from dual
  5    )

字符串
查询：

6  select acct_updt_tm, acct_id,
  7    nvl(acct_eff_dt, lag(acct_eff_dt) ignore nulls over (partition by acct_id order by acct_updt_tm)) acct_eff_dt
  8  from test
  9  order by acct_updt_tm desc;

ACCT_UPDT_TM           ACCT_ID ACCT_EFF_DT
------------------- ---------- -------------------
2023-10-24 23:04:35         56 2020-06-11 00:00:00
2023-10-14 19:34:11         56 2020-06-11 00:00:00
2023-10-14 19:12:03         56 2020-06-10 00:00:00

SQL>

型

[编辑]，基于您的评论：如果您想获取每个acct_id的 * 最新 * 值，请使用max聚合函数：

SQL> with test (acct_updt_tm, acct_id, acct_eff_dt) as
  2    (select date '2023-10-24', 56, null              from dual union all
  3     select date '2023-10-14', 56, date '2020-06-11' from dual union all
  4     --
  5     select date '2023-10-24', 41, null              from dual union all
  6     select date '2023-10-14', 41, date '2021-05-14' from dual
  7    )
  8  select acct_id, max(acct_updt_tm) acct_updt_tm, max(acct_eff_dt) acct_eff_dt
  9  from test
 10  group by acct_id
 11  order by acct_id desc;

   ACCT_ID ACCT_UPDT_ ACCT_EFF_D
---------- ---------- ----------
        56 2023-10-24 2020-06-11
        41 2023-10-24 2021-05-14

SQL>

型