Deleting Duplicate Records With Retaining First Occurance

denisvenis · #1 (**permalink**) 01-03-10, 04:03

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.

tonkuma · #2 (**permalink**) 01-03-10, 07:40

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

denisvenis · #3 (**permalink**) 01-03-10, 07:52

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.

tonkuma · #4 (**permalink**) 01-03-10, 08:13

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:

Quote:

Syntax alternatives and synonyms: For compatibility, the keywords DENSERANK
and ROWNUMBER can be used as synonyms for DENSE_RANK and
ROW_NUMBER respectively.

denisvenis · #5 (**permalink**) 01-04-10, 04:44

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.

tonkuma · #6 (**permalink**) 01-04-10, 05:04

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.

Stealth_DBA · #7 (**permalink**) 01-04-10, 09:20

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech