remove olde records grouping by three columns and ordering by date

this is the situation:

CREATE SEQUENCE SEQ_tmpdata MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL;

CREATE TABLE TMP_DATA ( 
     ID NUMBER(19,0) DEFAULT SEQ_tmpdata.nextval  NOT NULL
    ,COL_1  VARCHAR2(256 CHAR)
    ,COL_2  VARCHAR2(256 BYTE)
    ,COL_3  VARCHAR2(256 BYTE)
    ,COL_4  TIMESTAMP(6)
    ,COL_5  NUMBER(19,6)
    ,COL_6  VARCHAR2(256 BYTE)
);
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','MB', TO_TIMESTAMP('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,3141','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','RC', TO_TIMESTAMP('2020-06-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '15,8686','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','57', TO_TIMESTAMP('2020-02-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,362','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE32','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '29,32','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','AS', TO_TIMESTAMP('2020-05-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,5556','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P7','NO_VALUE', TO_TIMESTAMP('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2568','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A02','RA', TO_TIMESTAMP('2020-05-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4,4584','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE33','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '180','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','38', TO_TIMESTAMP('2020-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,6657','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','18', TO_TIMESTAMP('2020-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,5658','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE34','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '260','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RA', TO_TIMESTAMP('2020-05-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,127','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P7','NO_VALUE', TO_TIMESTAMP('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2568','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-04-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,0588','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE35','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '460','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','MB', TO_TIMESTAMP('2020-06-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '11,2971','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','NO_VALUE', TO_TIMESTAMP('2020-04-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '16,7299','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE36','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '220','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','38', TO_TIMESTAMP('2020-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,6657','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','48', TO_TIMESTAMP('2020-04-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,2402','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','30', TO_TIMESTAMP('2020-03-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,4043','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P10','MB', TO_TIMESTAMP('2020-05-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '15,0002','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','12', TO_TIMESTAMP('2020-04-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,8953','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A10','NO_VALUE', TO_TIMESTAMP('2020-04-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,9176','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','NO_VALUE', TO_TIMESTAMP('2020-03-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,336','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','18', TO_TIMESTAMP('2020-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,5658','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','57', TO_TIMESTAMP('2020-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4,2724','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '12,2274','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A10','NO_VALUE', TO_TIMESTAMP('2020-04-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,9176','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE37','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '229,32','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','8', TO_TIMESTAMP('2020-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,8931','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RC', TO_TIMESTAMP('2020-06-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,9942','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','49', TO_TIMESTAMP('2020-04-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2022','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RC', TO_TIMESTAMP('2020-06-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,9942','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','12', TO_TIMESTAMP('2020-04-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,8953','tabla_tmp_1');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-04-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,0588','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,6923','tabla_tmp_4');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE38','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '209,32','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE38','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '209,32','tabla_tmp_3');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');
insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '12,2274','tabla_tmp_4');

commit;

​x
 
CREATE SEQUENCE SEQ_tmpdata MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE  ORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL;​CREATE TABLE TMP_DATA (      ID NUMBER(19,0) DEFAULT SEQ_tmpdata.nextval  NOT NULL    ,COL_1  VARCHAR2(256 CHAR)    ,COL_2  VARCHAR2(256 BYTE)    ,COL_3  VARCHAR2(256 BYTE)    ,COL_4  TIMESTAMP(6)    ,COL_5  NUMBER(19,6)    ,COL_6  VARCHAR2(256 BYTE));insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','MB', TO_TIMESTAMP('2020-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,3141','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','RC', TO_TIMESTAMP('2020-06-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '15,8686','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','57', TO_TIMESTAMP('2020-02-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,362','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE32','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '29,32','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','AS', TO_TIMESTAMP('2020-05-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,5556','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P7','NO_VALUE', TO_TIMESTAMP('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2568','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A02','RA', TO_TIMESTAMP('2020-05-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4,4584','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE33','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '180','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','38', TO_TIMESTAMP('2020-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,6657','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','18', TO_TIMESTAMP('2020-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,5658','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE34','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '260','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RA', TO_TIMESTAMP('2020-05-08 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,127','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P7','NO_VALUE', TO_TIMESTAMP('2020-04-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2568','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-04-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,0588','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE35','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '460','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','MB', TO_TIMESTAMP('2020-06-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '11,2971','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','NO_VALUE', TO_TIMESTAMP('2020-04-16 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '16,7299','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE36','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '220','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','38', TO_TIMESTAMP('2020-02-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,6657','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','21', TO_TIMESTAMP('2020-03-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '6,1776','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','48', TO_TIMESTAMP('2020-04-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,2402','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','30', TO_TIMESTAMP('2020-03-27 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,4043','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P10','MB', TO_TIMESTAMP('2020-05-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '15,0002','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','12', TO_TIMESTAMP('2020-04-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,8953','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A10','NO_VALUE', TO_TIMESTAMP('2020-04-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,9176','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','NO_VALUE', TO_TIMESTAMP('2020-03-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '14,336','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','18', TO_TIMESTAMP('2020-02-25 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,5658','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','45', TO_TIMESTAMP('2020-05-09 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '8,5812','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P9','57', TO_TIMESTAMP('2020-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '4,2724','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '12,2274','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','A10','NO_VALUE', TO_TIMESTAMP('2020-04-14 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,9176','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE37','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '229,32','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RA', TO_TIMESTAMP('2020-06-29 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '2,555','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P11','8', TO_TIMESTAMP('2020-04-02 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,8931','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RC', TO_TIMESTAMP('2020-06-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,9942','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P4','49', TO_TIMESTAMP('2020-04-06 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '7,2022','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','RC', TO_TIMESTAMP('2020-06-20 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,9942','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P8','12', TO_TIMESTAMP('2020-04-05 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,8953','tabla_tmp_1');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-04-12 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '9,0588','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-23 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '5,6923','tabla_tmp_4');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE38','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '209,32','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('NO_VALUE','DE38','NO_VALUE', TO_TIMESTAMP('2019-06-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '209,32','tabla_tmp_3');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P3','RC', TO_TIMESTAMP('2020-06-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '13,7008','tabla_tmp_2');insert into tmp_data (col_1, col_2, col_3, col_4, col_5, col_6) values ('phone','P1','NO_VALUE', TO_TIMESTAMP('2020-03-26 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), '12,2274','tabla_tmp_4');​commit;​

The problem is: i need delete older records, grouping by col_2, col_3 and col_6 and ordering by col_4. To have the last element bet col_6.

the idea is have something like this:

I test this just for test delete olde record for ‘tabla_tmp_1’:

delete tmp_data f 
where  col_4 not in (
 select max(col_4)
 from   tmp_data s 
 where f.col_1 = s.col_1 
   and f.col_2 = s.col_2 
   and f.col_3 = s.col_3 
   and col_6 = 'tabla_tmp_1'
   and col_1 = 'phone' 
   and col_4 < to_date('27/09/2020','DD/MM/YYYY')
 );

 
delete tmp_data f where  col_4 not in ( select max(col_4) from   tmp_data s  where f.col_1 = s.col_1    and f.col_2 = s.col_2    and f.col_3 = s.col_3    and col_6 = 'tabla_tmp_1'   and col_1 = 'phone'    and col_4 < to_date('27/09/2020','DD/MM/YYYY') );​

but does’t work.

Can somebody help me with this?

Best regards

Answer

Use the ROW_NUMBER analytic function to find the rows that are not the latest in each group and then you can use the ROWID pseudo-column to correlate with in the DELETE:

DELETE FROM tmp_data
WHERE ROWID IN (
  SELECT rid
  FROM   (
    SELECT ROWID As rid,
           ROW_NUMBER() OVER (
             PARTITION BY col_2, col_3, col_6
             ORDER BY col_4 DESC
           ) AS rn
    FROM   tmp_data
  )
  WHERE rn > 1
)

 
DELETE FROM tmp_dataWHERE ROWID IN (  SELECT rid  FROM   (    SELECT ROWID As rid,           ROW_NUMBER() OVER (             PARTITION BY col_2, col_3, col_6             ORDER BY col_4 DESC           ) AS rn    FROM   tmp_data  )  WHERE rn > 1)​

Which deletes 22 rows.

db<>fiddle here

Advertisement

Answer