Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2008
    Posts
    29

    Unanswered: Deleting Duplicate Records With Retaining First Occurance

    Hi,

    See the query 'SELECT * FROM EMP' result:


    EMP_NO SALARY
    ==== ========
    1 100000
    1 100000
    1 100000
    2 200000
    2 200000
    3 300000
    3 300000
    4 400000
    4 400000
    4 400000

    There is no keys defined on above table.

    Now the requirement is to delete duplicates but retaining one occurance of each duplicate without using cursor:

    The O/P should be:

    EMP_NO SALARY
    ==== ========
    1 100000
    2 200000
    3 300000
    4 400000

    Is there any solution for this.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You didn't wrote DB2 version/release and platform on which your DB2 server is running.

    Here is an example on DB2 9.5 for LUW.
    Code:
    DELETE FROM emp1
     WHERE RID(emp1)
           IN (SELECT rid
                 FROM (SELECT RID(emp1) rid
                            , ROWNUMBER() OVER(PARTITION BY emp_no , salary) rn
                         FROM emp1 )
                WHERE rn > 1 )
    ;

  3. #3
    Join Date
    Dec 2008
    Posts
    29
    Hi Tonkuma,

    Thanks for your response.
    Sorry for not mentioning DB2 version,platform is 9.1 Z/os.
    Can we serve the same purpose without using RID and ROWNUMBER Function?

    Thanks,
    Denis.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I saw scalar function RID and OLAP specification ROW_NUMBER on "DB2 Version 9.1 for z/OS SQL Reference SC18-9854-05".

    I also saw the description:
    Syntax alternatives and synonyms: For compatibility, the keywords DENSERANK
    and ROWNUMBER can be used as synonyms for DENSE_RANK and
    ROW_NUMBER respectively.
    Last edited by tonkuma; 01-03-10 at 09:16.

  5. #5
    Join Date
    Dec 2008
    Posts
    29
    Hi tonkuma,

    Thanks for your reply.
    Actually I need to write this query without using RID and ROW_NUMBER function.
    Please suggest the solution.

    Thanks.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why do you need to write your query without using supported functionality?

    I'm not interested in that subject, at least now.

    There may be some other peoples who are interested in that.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I agree with tonkuma. If the functionality is available it should be used.

    The only other way I can think of to do this depends on the entire row being duplicated and not just the 2 columns you listed.

    If that is the case, you can select all the rows and GROUP by all the columns. This would summarize duplicate rows down to 1 row.
    Code:
    SELECT col1, col2, col3,..., colz
    FROM table-name
    GROUP BY col1, col2, col3,..., colz
    If this works, you can Unload the table with the SQL and then do a Load Replace.

    If the entire row is not duplicated, you will need some way to uniquely identify the duplicates so only one can be kept. This is what the ROWNUMBER() function was doing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •