Forum Stats

  • 3,740,431 Users
  • 2,248,255 Discussions
  • 7,861,250 Comments

Discussions

Reg: Duplicates records to delete.

chandra_1986
chandra_1986 Member Posts: 263 Blue Ribbon

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.

Best Answer

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond

    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
    mathguy Member Posts: 9,836 Gold Crown

    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
    chandra_1986 Member Posts: 263 Blue Ribbon

    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
    Frank Kulash Member, Moderator Posts: 40,700 Red Diamond

    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
    Paulzip Member Posts: 8,369 Blue Diamond
    edited Oct 12, 2020 12:42AM

    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
    chandra_1986 Member Posts: 263 Blue Ribbon

    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
    Solomon Yakobson Member Posts: 18,499 Black Diamond

    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.

    Paulzip
  • chandra_1986
    chandra_1986 Member Posts: 263 Blue Ribbon

    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
    chandra_1986 Member Posts: 263 Blue Ribbon

    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
    Paulzip Member Posts: 8,369 Blue Diamond

    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

    Frank Kulash
  • chandra_1986
    chandra_1986 Member Posts: 263 Blue Ribbon

    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
    chandra_1986 Member Posts: 263 Blue Ribbon

    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);

Sign In or Register to comment.