Skip to Content
0
Former Member
Sep 23, 2010 at 05:35 PM

SQL Query to identify duplicates

408 Views

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!