如何从重复记录列表返回最后更新的值?

Question

我正在努力获得最后更新的值(基于last_mod_date)的查询。这是我写的查询，但仍然没有得到想要的结果。

DROP TABLE IF EXISTS INVESTIGATIVE_ACTION_TABLE;
CREATE TEMPORARY TABLE INVESTIGATIVE_ACTION_TABLE AS(
SELECT DISTINCT t1.POLICY_NBR AS POLICY_ID, 
TRANS_TYP_DESC AS ACTION_TAKEN, 
MAX(LST_MDFD_DTM) AS LAST_MOD_DATE, 
LST_MDFD_USER_ID AS LAST_MOD_USER, 
MAX(CNTRCT_CNCL_EFCTV_DT) AS CANCEL_EFECT_DATE 
FROM POLICY_DETAILS AS t1
INNER JOIN CANC_EFCTV AS t2
ON t1.POLICY_NBR = t2.POLICY_NBR
WHERE t1.POLICY_NBR LIKE 'A%'
AND CNTRCT_CNCL_EFCTV_DT >= '2022-01-01'
AND LST_MDFD_DTM > '2022-01-01' --This range of date is only for example purposes
GROUP BY POLICY_ID, ACTION_TAKEN, LAST_MOD_USER
ORDER BY LAST_MOD_DATE DESC 
);

这是我运行查询

时得到的结果的示例问题是……既然"RESUME BILLING"是该身份证号的最后一次注册记录，我如何才能只获得"RESUME BILLING"?

提前感谢您的时间!!

Answer 1

一个包含示例数据的表(最好是基于文本的)和一个包含预期输出的表将有助于测试这一点，但是您将希望使用qualify函数。它将出现在您的group by表达式之后-或者更有可能您可以通过表达式完全消除该组。

您将需要在where子句之后:

qualify rownumber() over 
(partition by POLICY_ID, ACTION_TAKEN, LAST_MOD_USER
order by LST_MDFD_DTM desc) = 1

这将通过POLICY_ID, ACTION_TAKEN和LAST_MOD_USER创建窗口分区。在该分区中，它将以相反的顺序对行进行排序，并仅限定最近的更新。换句话说，不是最新的行将无法通过qualify测试，并且不会出现在最终结果集中。

如果你需要一个确切的答案，而不是一个一般的方法，请编辑问题，以更新一些样本数据。一般来说，当回答者根据描述创建自己的样本数据时，它可能会有点偏离。