Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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)
);
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';
BEGIN
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');
END;
/
1 rows affected

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
)
22 rows affected

SELECT * FROM tmp_data;


ID
COL_1
COL_2
COL_3
COL_4
COL_5
COL_6


2207
phone
P3
MB
2020-06-01 00:00:00.000000000
5,3141
tabla_tmp_2

2208
phone
P4
RC
2020-06-18 00:00:00.000000000
15,8686
tabla_tmp_2

2210
phone
P3
57
2020-02-19 00:00:00.000000000
8,362
tabla_tmp_1

2211
NO_VALUE
DE32
NO_VALUE
2019-06-01 00:00:00.000000000
29,32
tabla_tmp_3

2212
phone
P4
21
2020-03-12 00:00:00.000000000
6,1776
tabla_tmp_1

2213
phone
P3
AS
2020-05-11 00:00:00.000000000
14,5556
tabla_tmp_2

2214
phone
P7
NO_VALUE
2020-04-09 00:00:00.000000000
7,2568
tabla_tmp_4

2216
phone
P1
RA
2020-06-29 00:00:00.000000000
2,555
tabla_tmp_2

2218
phone
A02
RA
2020-05-17 00:00:00.000000000
4,4584
tabla_tmp_2

2219
NO_VALUE
DE33
NO_VALUE
2019-06-01 00:00:00.000000000
180
tabla_tmp_3

2220
phone
P11
38
2020-02-23 00:00:00.000000000
8,6657
tabla_tmp_1

2221
phone
P9
18
2020-02-25 00:00:00.000000000
2,5658
tabla_tmp_1

2222
NO_VALUE
DE34
NO_VALUE
2019-06-01 00:00:00.000000000
260
tabla_tmp_3

2223
phone
P3
RA
2020-05-08 00:00:00.000000000
14,127
tabla_tmp_2

2225
phone
P1
NO_VALUE
2020-04-12 00:00:00.000000000
9,0588
tabla_tmp_4

2226
NO_VALUE
DE35
NO_VALUE
2019-06-01 00:00:00.000000000
460
tabla_tmp_3

2227
phone
P4
MB
2020-06-18 00:00:00.000000000
11,2971
tabla_tmp_2

2228
phone
P8
NO_VALUE
2020-04-16 00:00:00.000000000
16,7299
tabla_tmp_4

2229
phone
P11
45
2020-05-09 00:00:00.000000000
8,5812
tabla_tmp_1

2230
NO_VALUE
DE36
NO_VALUE
2019-06-01 00:00:00.000000000
220
tabla_tmp_3

2233
phone
P8
48
2020-04-26 00:00:00.000000000
9,2402
tabla_tmp_1

2234
phone
P9
30
2020-03-27 00:00:00.000000000
8,4043
tabla_tmp_1

2236
phone
P10
MB
2020-05-05 00:00:00.000000000
15,0002
tabla_tmp_2

2237
phone
P8
12
2020-04-05 00:00:00.000000000
5,8953
tabla_tmp_1

2238
phone
A10
NO_VALUE
2020-04-14 00:00:00.000000000
13,9176
tabla_tmp_4

2239
phone
P3
RC
2020-06-26 00:00:00.000000000
13,7008
tabla_tmp_2

2243
phone
P9
57
2020-02-17 00:00:00.000000000
4,2724
tabla_tmp_1

2247
NO_VALUE
DE37
NO_VALUE
2019-06-01 00:00:00.000000000
229,32
tabla_tmp_3

2249
phone
P11
8
2020-04-02 00:00:00.000000000
9,8931
tabla_tmp_1

2250
phone
P1
RC
2020-06-20 00:00:00.000000000
9,9942
tabla_tmp_2

2251
phone
P4
49
2020-04-06 00:00:00.000000000
7,2022
tabla_tmp_1

2257
NO_VALUE
DE38
NO_VALUE
2019-06-01 00:00:00.000000000
209,32
tabla_tmp_3

ID	COL_1	COL_2	COL_3	COL_4	COL_5	COL_6
2207	phone	P3	MB	2020-06-01 00:00:00.000000000	5,3141	tabla_tmp_2
2208	phone	P4	RC	2020-06-18 00:00:00.000000000	15,8686	tabla_tmp_2
2210	phone	P3	57	2020-02-19 00:00:00.000000000	8,362	tabla_tmp_1
2211	NO_VALUE	DE32	NO_VALUE	2019-06-01 00:00:00.000000000	29,32	tabla_tmp_3
2212	phone	P4	21	2020-03-12 00:00:00.000000000	6,1776	tabla_tmp_1
2213	phone	P3	AS	2020-05-11 00:00:00.000000000	14,5556	tabla_tmp_2
2214	phone	P7	NO_VALUE	2020-04-09 00:00:00.000000000	7,2568	tabla_tmp_4
2216	phone	P1	RA	2020-06-29 00:00:00.000000000	2,555	tabla_tmp_2
2218	phone	A02	RA	2020-05-17 00:00:00.000000000	4,4584	tabla_tmp_2
2219	NO_VALUE	DE33	NO_VALUE	2019-06-01 00:00:00.000000000	180	tabla_tmp_3
2220	phone	P11	38	2020-02-23 00:00:00.000000000	8,6657	tabla_tmp_1
2221	phone	P9	18	2020-02-25 00:00:00.000000000	2,5658	tabla_tmp_1
2222	NO_VALUE	DE34	NO_VALUE	2019-06-01 00:00:00.000000000	260	tabla_tmp_3
2223	phone	P3	RA	2020-05-08 00:00:00.000000000	14,127	tabla_tmp_2
2225	phone	P1	NO_VALUE	2020-04-12 00:00:00.000000000	9,0588	tabla_tmp_4
2226	NO_VALUE	DE35	NO_VALUE	2019-06-01 00:00:00.000000000	460	tabla_tmp_3
2227	phone	P4	MB	2020-06-18 00:00:00.000000000	11,2971	tabla_tmp_2
2228	phone	P8	NO_VALUE	2020-04-16 00:00:00.000000000	16,7299	tabla_tmp_4
2229	phone	P11	45	2020-05-09 00:00:00.000000000	8,5812	tabla_tmp_1
2230	NO_VALUE	DE36	NO_VALUE	2019-06-01 00:00:00.000000000	220	tabla_tmp_3
2233	phone	P8	48	2020-04-26 00:00:00.000000000	9,2402	tabla_tmp_1
2234	phone	P9	30	2020-03-27 00:00:00.000000000	8,4043	tabla_tmp_1
2236	phone	P10	MB	2020-05-05 00:00:00.000000000	15,0002	tabla_tmp_2
2237	phone	P8	12	2020-04-05 00:00:00.000000000	5,8953	tabla_tmp_1
2238	phone	A10	NO_VALUE	2020-04-14 00:00:00.000000000	13,9176	tabla_tmp_4
2239	phone	P3	RC	2020-06-26 00:00:00.000000000	13,7008	tabla_tmp_2
2243	phone	P9	57	2020-02-17 00:00:00.000000000	4,2724	tabla_tmp_1
2247	NO_VALUE	DE37	NO_VALUE	2019-06-01 00:00:00.000000000	229,32	tabla_tmp_3
2249	phone	P11	8	2020-04-02 00:00:00.000000000	9,8931	tabla_tmp_1
2250	phone	P1	RC	2020-06-20 00:00:00.000000000	9,9942	tabla_tmp_2
2251	phone	P4	49	2020-04-06 00:00:00.000000000	7,2022	tabla_tmp_1
2257	NO_VALUE	DE38	NO_VALUE	2019-06-01 00:00:00.000000000	209,32	tabla_tmp_3