Eliminating duplicate rows from a table

Some quick SQL to eliminate duplicate rows from a table in a DB2 DB.

If you have a table like this...

ID          NAME       AGE         BIRTHDATE 
----------- ---------- ----------- ---------- 
          1 venkat              30 05/10/1971 
          2 ve                  30 05/10/1970 
          1 venkat              25 05/10/1975 
          3 vetest              29 05/10/1969 

...here's how to delete duplicate entries while keeping the newest entry row. First, create a view over the table (this is needed because DELETE cannot range over a query):

CREATE VIEW temp(rn) AS SELECT rownumber() OVER (PARTITION BY id ORDER BY birthdate DESC) 
         FROM test; 

Now delete the duplicates:

DELETE FROM temp WHERE rn > 1; 

Now check the output:

select * from test order by id; 
      
ID          NAME       AGE         BIRTHDATE 
----------- ---------- ----------- ---------- 
          1 venkat              25 05/10/1975 
          2 ve                  30 05/10/1970 
          3 vetest              29 05/10/1969 

  3 record(s) selected. 

For More Information


This was first published in December 2001

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close