topic Re: SQL Query to identify duplicates in Application Development and Automation Discussions

SQL Query to identify duplicates

Former Member — Thu, 23 Sep 2010 17:35:19 GMT

Hi All,

I want to identify and display some duplicate data from the database.

With duplicates I mean rows in a table that contain identical information in a combination of fields (Last Name, First Name, City).

Example:

ID1 ID2 Last Name First Name City Phone

1005 2010 Krieger Jeff San Ramon 9252997100

1012 2010 Krieger Jeff San Ramon 9252997100

1017 2010 Krieger Jeff San Ramon 9252997100

Now I want to select the IDs (ID1 and ID2) that identify each duplicate rows.

Therefore I want to create a query with a subquery inside, similar to this:

SELECT ID1 ID2 LastName FirstName

FROM Customers

INTO TABLE

WHERE (LastName FirstName City) IN

(SELECT LastName FirstName City

FROM Customers

GROUP BY LastName FirstName City

HAVING COUNT(*) > 1)

ORDER BY LastName FirstName.

How would be the syntax in ABAP, because I am not able to define more than one field after WHERE?

Any Ideas how to identify the duplicate data and return the ids?

Thanks for any help!

Re: SQL Query to identify duplicates

Former Member — Thu, 23 Sep 2010 17:39:41 GMT

I think you will have to write individual WHERE's for each condition and splice them together with ANDs

Rob

I originall ysaid OR but meant AND

Edited by: Rob Burbank on Sep 23, 2010 1:40 PM

Re: SQL Query to identify duplicates

brad_bohn — Thu, 23 Sep 2010 18:42:36 GMT

Is this something you need to do via code or can you use an analysis tool? TAANA & TAANA_AV allow you to analyze distribution spreads by table fields (you can pick your table and the fields). It's primarily used for archiving analysis but it's helpfuul in other scenarios as well. It's not optimal for finding single record dupes but I thought I'd throw it out there.