Reg: Duplicates records to delete.

chandra_1986

HI Experts,

using below query from table: select * from hourly_report_table where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;

having duplicate records in table:

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225548611 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225452770 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

Kindly suggest how to delete the duplicate records.

Solomon Yakobson

You could create index on API_DATE, API_HOUR, TOTAL_TRANS and use

DELETE HOURLY_REPORT_DUPLICATE_DELY T1
  WHERE T1.ID < (
                 SELECT  MAX(T2.ID)
                   FROM  HOURLY_REPORT_DUPLICATE_DELY T2
                   WHERE T2.API_DATE = T1.API_DATE
                     AND T2.API_HOUR = T1.API_HOUR
                     AND T2.TOTAL_TRANS = T1.TOTAL_TRANS
                   HAVING COUNT(*) > 1
                )
/

But I'd create exceptions table and then issue create unique constraint on API_DATE, API_HOUR, TOTAL_TRANS with EXCEPTIONS INTO clause, delete duplicated and enforce unique constraint to prevent future duplicates:

SQL> @?\RDBMS\ADMIN\UTLEXCPT.SQL


Table created.


SQL> DESC EXCEPTIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 CONSTRAINT                                         VARCHAR2(128)


SQL> ALTER TABLE HOURLY_REPORT_DUPLICATE_DELY
  2    ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
  3      UNIQUE(API_DATE,API_HOUR,TOTAL_TRANS)
  4      EXCEPTIONS INTO EXCEPTIONS
  5  /
  ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
                 *
ERROR at line 2:
ORA-02299: cannot validate (SCOTT.HOURLY_REPORT_DUPLICATE_DELY_UK) - duplicate
keys found




SQL> SELECT  COUNT(*)
  2    FROM  EXCEPTIONS
  3    WHERE OWNER = 'SCOTT'
  4      AND TABLE_NAME = 'HOURLY_REPORT_DUPLICATE_DELY'
  5      AND CONSTRAINT = 'HOURLY_REPORT_DUPLICATE_DELY_UK'
  6  /


            COUNT(*)
--------------------
                   5


SQL> DELETE HOURLY_REPORT_DUPLICATE_DELY
  2   WHERE ROWID IN (
  3                   SELECT  ROW_ID
  4                     FROM  EXCEPTIONS
  5                     WHERE OWNER = 'SCOTT'
  6                       AND TABLE_NAME = 'HOURLY_REPORT_DUPLICATE_DELY'
  7                       AND CONSTRAINT = 'HOURLY_REPORT_DUPLICATE_DELY_UK'
  8                  )
  9  /


5 rows deleted.


SQL> ALTER TABLE HOURLY_REPORT_DUPLICATE_DELY
  2    ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
  3        UNIQUE(API_DATE,API_HOUR,TOTAL_TRANS)
  4  /


Table altered.


SQL>

SY.

Frank Kulash

hi,

Whenever you have a question, please post a little sample data in a usable form (such as CREATE TABLE and INSERT statements), so the people who want to help you car re-create the problem and test their ideas. Also post the exact results you want from the given data, and an explanation (in general terms) of you get those results from that data.  In the case of a DML problem (such as DELETE) the sample data should show the table as it exists berore the DML, and the results will be the contents of the changed table after the DML is finished. What is a "record" in this problem? How can you tell if "record" A is a "duplcate" of "record" B? Always say which version of Oracle you're using (e.g. 12.2.0.1.0).

See: https://community.oracle.com/tech/apps-infra/kb/articles/4-how-to-ask-questions-in-developer-community-spaces

mathguy

You asked the same question several times before. Didn't you get enough usable answers already?

And, if you never got a good enough answer, why do you expect that this time will be different?

Separately: Your question titles often begin with Reg: What does that mean? And, even assuming it does mean something: does it ever help someone who reads the title understand more about your question?

chandra_1986

WE ARE USING : 12.1.0.2.0

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

SAMPLE DATA IN TABLE, ABOVE IS COLUMN NAME AND BELOW CORRESPONDING VALUES, BELOW VALUES ARE HAVING IDENTICAL, SO WE NEED TO KEEP ONE AND REMOVE ALL OTHER DUPLICATES VALUES FROM RECORDS.

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225548611 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225452770 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

TABLE NAME: hourly_report_table 

Frank Kulash

Thanks for posting your Oracle version.

Don't forget to post CREATE TABLE and INSERT statements for the sample data and the desired results, and to answer the questions in the first two replies.

Paulzip

Duplicate of this

Why are you breaking forum etiquette and posting the same question again?

The reason you don't get replies are because you do the same thing over and over again:

• Little / vague information, requirements never fully described.
• Minimal personal effort
• No create table or inserts
• Moving the goal posts.
• Duplicate posts.
• Serial offender of forum etiquette.

chandra_1986

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

Having duplicate record in table hourly_report_table, which query needs to use to delete the duplicate records and keeping one .

Solomon Yakobson

Which part of please provide CREATE TABLE and INSERT statements for the sample, desired results and logic how to get desired results is so difficult to understand?

SY.

chandra_1986

Having duplicate records with decimal values so like below, with ID only unique values, tried with query below but not working, kindly suggest how to delete duplicate records by keeping one record in database, when the records present with decimal values in table.

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

tried with query below, but no output.

 select * from hourly_report_table 

 Where Id Not in

   (Select max(Id)

    from hourly_report_table  

    group by APPLICATION, API_DATE, API_HOUR, SO, APP, API, ACTUAL_API, AVG_RUN, AVG_RUN_GOOD, AVG_RUN_FAIL, 

TOTAL_TRANS, GOOD_TRANS, FAIL_TRANS, FAIL_PERC, COUNTS_TO1, PERC_TO1, COUNTS_TO15, PERC_TO15, COUNTS_OVER15, PERC_OVER15, 

COUNTS_1TO5, PERC_1TO5, COUNTS_5TO10, PERC_5TO10, COUNTS_10TO15, PERC_10TO15, COUNTS_15TO30, PERC_15TO30, COUNTS_30TO60, 

PERC_30TO60, COUNTS_OVER60, PERC_OVER60, CREATED_USER_ID, CREATED_TIME_STAMP, METRIC, AVG_RUN_GOOD, AVG_RUN_FAIL)

and API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;

chandra_1986

We can create table with below columns and records:

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

As we can see duplicate records are present with decimal values, so how can we delete these duplicates by keeping only record from these 3 records...i tried above delete query, before deleting tried with select statmenet but no output from below query,but above records are present with API_HOUR=9 and date='2020-09-20' and total_trans=72506.

select * from hourly_report_table 

 Where Id Not in

   (Select max(Id)

    from hourly_report_table  

    group by APPLICATION, API_DATE, API_HOUR, SO, APP, API, ACTUAL_API, AVG_RUN, AVG_RUN_GOOD, AVG_RUN_FAIL, 

TOTAL_TRANS, GOOD_TRANS, FAIL_TRANS, FAIL_PERC, COUNTS_TO1, PERC_TO1, COUNTS_TO15, PERC_TO15, COUNTS_OVER15, PERC_OVER15, 

COUNTS_1TO5, PERC_1TO5, COUNTS_5TO10, PERC_5TO10, COUNTS_10TO15, PERC_10TO15, COUNTS_15TO30, PERC_15TO30, COUNTS_30TO60, 

PERC_30TO60, COUNTS_OVER60, PERC_OVER60, CREATED_USER_ID, CREATED_TIME_STAMP, METRIC, AVG_RUN_GOOD, AVG_RUN_FAIL)

and API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;

Paulzip

If anyone here runs the lines below, do they get your tables created with data in them? No. So stop being so obtuse all the time and put a little effort in for once. You are one of the worst offenders on this forum for total laziness and vagueness. For the umpteenth time : POST CREATE TABLE AND INSERT SCRIPTS. Also post what rules you want in terms of duplicates. In other words, which record do you want to preserve and why? The one with the highest ID?

ID APPLICATION API_DATE API_HOUR SO APP API ACTUAL_API AVG_RUN TOTAL_TRANS GOOD_TRANS FAIL_TRANS FAIL_PERC COUNTS_TO1 PERC_TO1 COUNTS_TO15 PERC_TO15 COUNTS_OVER15 PERC_OVER15 COUNTS_1TO5 PERC_1TO5 COUNTS_5TO10 PERC_5TO10 COUNTS_10TO15 PERC_10TO15 COUNTS_15TO30 PERC_15TO30 COUNTS_30TO60 PERC_30TO60 COUNTS_OVER60 PERC_OVER60 CREATED_USER_ID CREATED_TIME_STAMP METRIC AVG_RUN_GOOD AVG_RUN_FAIL

225344087 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

225278469 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

224980737 LS 30-Sep-20 9 G2 GetCustomerSnapshot GetCustomerSnapshot 0.176920834 72506 72505 1 1.3792E-05 72007 0.993117811 72477 0.999600033 29 0.000399967 403 0.005558161 52 0.000717182 15 0.000206879 12 0.000165504 13 0.000179296 4 5.51678E-05 UFOSODRPT 4-Oct-20 A 0.176561258 20.256

chandra_1986

HI Experts,

please find the create and insert comannds:

create table hourly_report_duplicate_dely(

ID              NUMBER   NOT NULL,     

APPLICATION         VARCHAR2(32 CHAR),  

API_DATE          DATE,        

API_HOUR          NUMBER,       

SO             VARCHAR2(100 CHAR), 

APP             VARCHAR2(100 CHAR), 

API             VARCHAR2(100 CHAR), 

ACTUAL_API         VARCHAR2(100 CHAR), 

AVG_RUN           NUMBER,       

TOTAL_TRANS         NUMBER,       

GOOD_TRANS         NUMBER,       

FAIL_TRANS         NUMBER,       

FAIL_PERC          NUMBER,       

COUNTS_TO1         NUMBER,       

PERC_TO1          NUMBER,       

COUNTS_TO15         NUMBER,       

PERC_TO15          NUMBER,       

COUNTS_OVER15        NUMBER,       

PERC_OVER15         NUMBER,       

COUNTS_1TO5         NUMBER,       

PERC_1TO5          NUMBER,       

COUNTS_5TO10        NUMBER,       

PERC_5TO10         NUMBER,       

COUNTS_10TO15        NUMBER,       

PERC_10TO15         NUMBER,       

COUNTS_15TO30        NUMBER,       

PERC_15TO30         NUMBER,       

COUNTS_30TO60        NUMBER,       

PERC_30TO60         NUMBER,       

COUNTS_OVER60        NUMBER,       

PERC_OVER60         NUMBER,       

CREATED_USER_ID       VARCHAR2(10 CHAR),  

CREATED_TIME_STAMP     DATE,        

METRIC           VARCHAR2(6),     

AVG_RUN_GOOD        NUMBER,       

AVG_RUN_FAIL        NUMBER );

insert into hourly_report_duplicate_dely values(    

102615547, 'LS', '30-SEP-20', 9, 'NULL', 'G2', 'GetCustomerSnapshot', 'GetCustomerSnapshot', 0.176920834137864, 72506, 72505, 1, 0.0000137919620445205, 72007, 0.993117810939784, 72477, 0.999600033100709, 29, 0.000399966899291093, 403, 0.00555816070394174, 52,   0.000717182026315064, 15 ,0.000206879430667807 ,12 ,0.000165503544534245 ,13 ,0.000179295506578766 ,4 ,0.0000551678481780818 ,'UFOSODRPT' ,'30-SEP-20' ,'A' ,0.176561257844287 ,20.256);

insert into hourly_report_duplicate_dely values(

102910503, 'LS', '30-SEP-20', 9, 'NULL', 'G2', 'GetCustomerSnapshot', 'GetCustomerSnapshot', 0.176920834137864, 72506, 72505, 1, 0.0000137919620445205, 72007, 0.993117810939784, 72477, 0.999600033100709, 29, 0.000399966899291093, 403, 0.00555816070394174, 52,   0.000717182026315064, 15 ,0.000206879430667807 ,12 ,0.000165503544534245 ,13 ,0.000179295506578766 ,4 ,0.0000551678481780818 ,'UFOSODRPT' ,'30-SEP-20' ,'A' ,0.176561257844287 ,20.256);

insert into hourly_report_duplicate_dely values(

102761598, 'LS', '30-SEP-20', 9, 'NULL', 'G2', 'GetCustomerSnapshot', 'GetCustomerSnapshot', 0.176920834137864, 72506, 72505, 1, 0.0000137919620445205, 72007, 0.993117810939784, 72477, 0.999600033100709, 29, 0.000399966899291093, 403, 0.00555816070394174, 52,   0.000717182026315064, 15 ,0.000206879430667807 ,12 ,0.000165503544534245 ,13 ,0.000179295506578766 ,4 ,0.0000551678481780818 ,'UFOSODRPT' ,'30-SEP-20' ,'A' ,0.176561257844287 ,20.256);

insert into hourly_report_duplicate_dely values(

103062309, 'LS', '30-SEP-20', 9, 'NULL', 'G2', 'GetCustomerSnapshot', 'GetCustomerSnapshot', 0.176920834137864, 72506, 72505, 1, 0.0000137919620445205, 72007, 0.993117810939784, 72477, 0.999600033100709, 29, 0.000399966899291093, 403, 0.00555816070394174, 52,   0.000717182026315064, 15 ,0.000206879430667807 ,12 ,0.000165503544534245 ,13 ,0.000179295506578766 ,4 ,0.0000551678481780818 ,'UFOSODRPT' ,'30-SEP-20' ,'A' ,0.176561257844287 ,20.256);

insert into hourly_report_duplicate_dely values(

103217040, 'LS', '30-SEP-20', 9, 'NULL', 'G2', 'GetCustomerSnapshot', 'GetCustomerSnapshot', 0.176920834137864, 72506, 72505, 1, 0.0000137919620445205, 72007, 0.993117810939784, 72477, 0.999600033100709, 29, 0.000399966899291093, 403, 0.00555816070394174, 52,   0.000717182026315064, 15 ,0.000206879430667807 ,12 ,0.000165503544534245 ,13 ,0.000179295506578766 ,4 ,0.0000551678481780818 ,'UFOSODRPT' ,'30-SEP-20' ,'A' ,0.176561257844287 ,20.256);

Result need to keep only one records as original table have millions of records....please suggest.

chandra_1986

tried with below query to delete before that to select and check , but no results from select statement.

 select * from hourly_report_duplicate_dely 

 Where rowid Not in

   (Select min(rowid)

    from hourly_report_duplicate_dely -- where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506

    group by ID, APPLICATION, API_DATE, API_HOUR, SO, APP, API, ACTUAL_API, AVG_RUN, AVG_RUN_GOOD, AVG_RUN_FAIL, 

TOTAL_TRANS, GOOD_TRANS, FAIL_TRANS, FAIL_PERC, COUNTS_TO1, PERC_TO1, COUNTS_TO15, PERC_TO15, COUNTS_OVER15, PERC_OVER15, 

COUNTS_1TO5, PERC_1TO5, COUNTS_5TO10, PERC_5TO10, COUNTS_10TO15, PERC_10TO15, COUNTS_15TO30, PERC_15TO30, COUNTS_30TO60, 

PERC_30TO60, COUNTS_OVER60, PERC_OVER60, CREATED_USER_ID, CREATED_TIME_STAMP, METRIC, AVG_RUN_GOOD, AVG_RUN_FAIL);

Solomon Yakobson

You could create index on API_DATE, API_HOUR, TOTAL_TRANS and use

DELETE HOURLY_REPORT_DUPLICATE_DELY T1
  WHERE T1.ID < (
                 SELECT  MAX(T2.ID)
                   FROM  HOURLY_REPORT_DUPLICATE_DELY T2
                   WHERE T2.API_DATE = T1.API_DATE
                     AND T2.API_HOUR = T1.API_HOUR
                     AND T2.TOTAL_TRANS = T1.TOTAL_TRANS
                   HAVING COUNT(*) > 1
                )
/

But I'd create exceptions table and then issue create unique constraint on API_DATE, API_HOUR, TOTAL_TRANS with EXCEPTIONS INTO clause, delete duplicated and enforce unique constraint to prevent future duplicates:

SQL> @?\RDBMS\ADMIN\UTLEXCPT.SQL


Table created.


SQL> DESC EXCEPTIONS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROW_ID                                             ROWID
 OWNER                                              VARCHAR2(128)
 TABLE_NAME                                         VARCHAR2(128)
 CONSTRAINT                                         VARCHAR2(128)


SQL> ALTER TABLE HOURLY_REPORT_DUPLICATE_DELY
  2    ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
  3      UNIQUE(API_DATE,API_HOUR,TOTAL_TRANS)
  4      EXCEPTIONS INTO EXCEPTIONS
  5  /
  ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
                 *
ERROR at line 2:
ORA-02299: cannot validate (SCOTT.HOURLY_REPORT_DUPLICATE_DELY_UK) - duplicate
keys found




SQL> SELECT  COUNT(*)
  2    FROM  EXCEPTIONS
  3    WHERE OWNER = 'SCOTT'
  4      AND TABLE_NAME = 'HOURLY_REPORT_DUPLICATE_DELY'
  5      AND CONSTRAINT = 'HOURLY_REPORT_DUPLICATE_DELY_UK'
  6  /


            COUNT(*)
--------------------
                   5


SQL> DELETE HOURLY_REPORT_DUPLICATE_DELY
  2   WHERE ROWID IN (
  3                   SELECT  ROW_ID
  4                     FROM  EXCEPTIONS
  5                     WHERE OWNER = 'SCOTT'
  6                       AND TABLE_NAME = 'HOURLY_REPORT_DUPLICATE_DELY'
  7                       AND CONSTRAINT = 'HOURLY_REPORT_DUPLICATE_DELY_UK'
  8                  )
  9  /


5 rows deleted.


SQL> ALTER TABLE HOURLY_REPORT_DUPLICATE_DELY
  2    ADD CONSTRAINT HOURLY_REPORT_DUPLICATE_DELY_UK
  3        UNIQUE(API_DATE,API_HOUR,TOTAL_TRANS)
  4  /


Table altered.


SQL>

SY.