Display only the duplicate records in SQL

50 pts.
Tags:
SQL
SQL Query
I need a query to display only the duplicate records alone. For an example, distinct filtr the duplicate records and displays the actual record. Here I need only duplicate records.
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

GROUP BY along with HAVING count(*)>1 will give results where more than one row satisfies the grouping. That’s essentially the practical definition of “duplicate”.

But it can depend on exact requirements. A SELECT DISTINCT is intended to filter out duplications of entire rows. First question to answer should focus on the source of the duplicates.

Are these rows logical duplicates in a result set that come perhaps through various JOINs or UNIONs? Or are they physically present in source tables?

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • CharlieBrowne

    In your script, create a table that just has the fields you are checking for duplicates and a counter field

    Now insert into that table a single record using distinct, with the count.

    Now select all records fro your primary file joining your work file selecting where counter > 1

    62,385 pointsBadges:
    report
  • carlosdl
    How about a group by clause on all of the columns and a condition in the HAVING clause to see thos with count(*) > 1?

    What have you tried?

    If you expect more accurate suggestions, please post the exact structure of your tables, some sample data, and an example of the output you would like to get from that data.
    84,745 pointsBadges:
    report
  • ToddN2000
    You could also use the EXISTS method. What have you tried and what errors have you gotten?
    132,760 pointsBadges:
    report

Forgot Password

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

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: