add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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');
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