Gurus,
I am trying to write a query to identify the max (inserted date) records for each ID. Once those records are identified, I want to set the active indicator to 0 for the remaining dates. Below is an example.
In the above pic, M_ID 123456 has ACT_IND 1 for 2 ID_NO 1 & 2 with different inserted dates. I want to identify the max inserted date record and update the other record to ACT_IND=0. Which means ID_No 2 should only have ACT_IND=1 as it has max inserted date of 23 oct 21. ID_No 1 should have ACT_IND=0.
Similarly for M_ID 234567 as three records having ACT_IND=1. Of the three, only the latest record which is ID_NO 5 inserted on 12-JUL-21 should have ACT_IND=1 and the other should be 0.
There could be only one active record with ACT_IND=1 but can be ignored. The issue is that somehow need to identify the M_ID's that have multiple inserted dates, retain the max inserted date active indicator to 1 and set the other to 0.
Expected output as below.
Scripts as below:
create table test_data
(
id_no int,
m_id varchar2(20 byte) ,
act_ind int,
inserted_dt date);
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('1', '123456', '1', TO_DATE('2021-10-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('2', '123456', '1', TO_DATE('2021-10-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('3', '234567', '1', TO_DATE('2021-07-10 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('4', '234567', '1', TO_DATE('2021-07-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('5', '234567', '1', TO_DATE('2021-07-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('6', '345678', '1', TO_DATE('2021-04-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('7', '334455', '1', TO_DATE('2021-01-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO "TEST_DATA" (ID_NO, M_ID, ACT_IND, INSERTED_DT) VALUES ('8', '334455', '1', TO_DATE('2021-05-22 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));
COMMIT;
Any suggestions please?