Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Active records by max insert date

Akbar JalaluddinOct 26 2021

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.
image.png
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.
image.pngScripts 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?

Comments

Processing

Post Details

Added on Oct 26 2021
14 comments
326 views