var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Deleting Duplicate Records With Retaining First Occurance
See the query 'SELECT * FROM EMP' result:
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:
Is there any solution for this.
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.
DELETE FROM emp1
IN (SELECT rid
FROM (SELECT RID(emp1) rid
, ROWNUMBER() OVER(PARTITION BY emp_no , salary) rn
FROM emp1 )
WHERE rn > 1 )
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?
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
can be used as synonyms for DENSE_RANK
Last edited by tonkuma; 01-03-10 at 08:16.
Thanks for your reply.
Actually I need to write this query without using RID and ROW_NUMBER function.
Please suggest the solution.
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.
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.
If this works, you can Unload the table with the SQL and then do a Load Replace.
SELECT col1, col2, col3,..., colz
GROUP BY col1, col2, col3,..., colz
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.