CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-sqldeveloper
singh_dba
singh_dba

Reputation: 31

How to delete duplicate records when one columns having unique value

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 record in table below, how can we delete it:

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

Thanks tried putting requsted changes, but it's deleting all 5 records from table somehow:-

delete  from hourly_report_table
    where id not in (select id
                     from (select max(id) id, application, api_date, api_hour, so
                           from test
                           group by application, api_date, api_hour, so
                          )
                     ) and API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506;
                     
           5 rows deleted.

Thanks, Tried with requested command again, but again it's giving data in output from 23-03-2017 onwards.

   select *  from hourly_report_table 
Where Id Not in
          (Select max(Id)
            from hourly_report_table  where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506
            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);
        
        
        
24134557    TSNR    23-MAR-17   3       CSI InquireWirelineServiceMaintenanceDetails_POTSWtn    InquireWirelineServiceMaintenanceDetails_POTSWtn    1.344
24134558    TSNR    23-MAR-17   3       RTTP    STB_SEND_MESSAGE    RTTPSendMessageToSTB    1.099
24134559    TSNR    23-MAR-17   3       CSI InquireFiberServiceOrderDetail_Detail   InquireFiberServiceOrderDetail_Detail   0.976820512820513
24134560    TSNR    23-MAR-17   3       CMS GetLiveData_5031NV-030  GetLiveData_5031NV-030  20.828

Below are the records present in table hourly_report_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



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

Found decimal values in following columns:

FAIL_PERC : 0.0000137919620445205 PERC_T01 : 0.993117810939784 PERC_T015: 0.999600033100 PERC_OVER15=0.0003999 AVG_RUN_GOOD : 0.17656 AVG_RUN_FAIL: 20.256

TABLE NAME: hourly_report_table

Upvotes: 1

Views: 125

Answers (2)

Somy
Somy

Reputation: 1634

Let’s say that unique column is “Id” and the duplicate columns are col1, col2 & col3 (Table : myTable) ; you can simple do -

Delete from myTable
Where Id Not in
          (Select max(Id)
            from myTable
            group by col1, col2, col3);

EDIT : This should work fine for high volume of records as well.

UPDATE : You should also specify your where conditions outside, otherwise it would consider all the ids that doesn’t come out of the sub-query. This is to make sure that the Id not in condition is applied to a subset of records that satisfies the other set of criteria and not applied globally across the board. Please refer below.

 select *  from hourly_report_table 
 Where Id Not in
      (Select max(Id)
        from hourly_report_table  where API_HOUR=9 and API_DATE=date '2020-09-30' and total_trans=72506
        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;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 143103

As you want to keep only one row (which one? Let's suppose the one whose ID is MAX for those "duplicate" values), then this might be one option:

Sample data:

SQL> select * From test;

        ID AP API_DATE     API_HOUR SO
---------- -- ---------- ---------- --
      4087 LS 2020-09-30          9 G2
      8469 LS 2020-09-30          9 G2
       737 LS 2020-09-30          9 G2
      8611 XX 2020-05-30          2 G1
      2770 XX 2020-05-30          2 G1

Delete duplicates:

SQL> delete from test
  2  where id not in (select max(id)
  3                   from test
  4                   group by application, api_date, api_hour, so
  5                  );

3 rows deleted.

What remains?

SQL> select * From test;

        ID AP API_DATE     API_HOUR SO
---------- -- ---------- ---------- --
      8469 LS 2020-09-30          9 G2
      8611 XX 2020-05-30          2 G1

SQL>

Upvotes: 1

Related Questions