Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Active records by max insert date

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?
Answers
-
And what have you tried?
Seems to me that if the business requirement is that, for a given M_ID, only the latest INSERTED_DTE is to be considered 'active', then you really don't even need the indicator. The record is defined as 'active' merely by being the one with the latest INSERTED_DTE.
-
Correct, only the latest INSERTED_DATE should have ACT_IND=1 rest all should have ACT_IND=0 for a given M_ID.
SELECT M_ID,COUNT(*)
FROM TEST_DATA
GROUP BY M_ID
HAVING COUNT(*)>1;
The above query should not give any records but due to duplicates, I am getting records.
-
Assuming you have no ties on INSERTED_DT for an M_ID and INSERTED_DT is not nullable:
select id_no, m_id , case when inserted_dt < max(inserted_dt) over (partition by m_id) then 0 else 1 end act_ind , inserted_dt from test_data / ID_NO|M_ID | ACT_IND|INSERTED_DT ----------|-------|----------|----------- 1|123456 | 0|22/10/2021 2|123456 | 1|23/10/2021 3|234567 | 0|10/07/2021 4|234567 | 0|11/07/2021 5|234567 | 1|12/07/2021 7|334455 | 0|22/01/2021 8|334455 | 1|22/05/2021 6|345678 | 1|22/04/2021 8 rows selected.
-
Thank you @Paulzip, but how to issue an update statement on the table to update the records? I want to update the data in the table.
UPDATE TEST_DATA
SET ACT_IND=0
WHERE INSERTED_DT<MAX(INSERTED_DT) OVER (PARTITION BY M_ID); I am getting group function not allowed here. Looks like I can't use max function correct?
-
Thank you. Yes, ID_NO is the primary key. It seems the data manipulation is not legal.
-
Hi, @User_DIL60
I am getting group function not allowed here.
Right; analytic functions are computed after the WHERE clause is applied, so you can never use them in a WHERE clause. Here's one way that uses the aggregate MAX function:
UPDATE test_data SET act_ind = 0 WHERE act_ind = 1 AND (m_id, inserted_dt) NOT IN ( SELECT m_id , MAX (inserted_dt) FROM test_data WHERE act_ind = 1 GROUP BY m_id ) ;
What do you want to do in case of ties? When the latest inserted_dt is not unique, the statement above leaves all the rows that have the latest inserted_dt with act_id=1.
-
Thank you @Frank Kulash Correct, all the latest inserted date records no matter the active indicator status (either 0 or 1)should have an active indicator as 1. Rest of the records should have an active indicator 0.
Like in the above example, M_ID 123456 with oldest inserted date 22 oct 21 is set to 0 where as 23 oct 21 should have active indicator 1. Similary, for M_ID 2345677 only latest inserted date 12-jul-21 should have active indicator 1 and all other records for that M_ID should be set to 0.
-
Hi, @User_DIL60
all the latest inserted date records no matter the active indicator status (either 0 or 1) ...
In that case, it would make sense to test with some sample data where act_id=0, rather than having act_id=1 on all rows. Here's one way to do it:
MERGE INTO test_data dst USING ( SELECT id_no , CASE RANK () OVER ( PARTITION BY m_id ORDER BY inserted_dt DESC ) WHEN 1 THEN 1 ELSE 0 END AS act_ind FROM test_data ) src ON (dst.id_no = src.id_no) WHEN MATCHED THEN UPDATE SET dst.act_ind = src.act_ind WHERE dst.act_ind <> src.act_ind ;
assuming id_no is unique.
-
Hi, @User_DIL60
It seems like you should be able to use MERGE, where the USING clause is just what Paulzip posted earlier:
MERGE INTO test_data dst USING ( SELECT id_no, m_id , CASE WHEN inserted_dt < MAX (inserted_dt) OVER (PARTITION BY m_id) THEN 0 ELSE 1 END AS act_ind , inserted_dt FROM test_data ) src ON (dst.id_no = src.id_no) WHEN MATCHED THEN UPDATE SET dst.act_ind = src.act_ind WHERE dst.act_ind <> src.act_ind ;
but when I try it, I get this error:
WHEN inserted_dt < MAX (inserted_dt) OVER (PARTITION BY m_id) * ERROR at line 5: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
I'm using Oracle 18.4.0.0.0. I wonder if this works in outher versions.
-
Hi,
I am not sure at what stage do you want to update the records, but the below can be one of the option assuming that the latest record will always have the latest insert date.
Use a BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER TEST_TRG
BEFORE INSERT
ON TEST_DATA
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
UPDATE TEST_DATA
SET ACT_IND = 0
WHERE M_ID = :NEW.M_ID
AND ACT_IND = 1;
:NEW.ACT_IND := 1;
END ;
/