Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table with duplicates and I want to present it to the stakeholders so they can help me determine the source of duplication. Currently I can print 1 row for each duplicate entry, but I want to show all attributes for each. Here's the query I'm using:

select *, count(*), max(id) from foos group by name having count(*) > 1 order by name;

I'm not sure how to alter the query to get each record where another record shares a name. Thanks for any guidance.

share|improve this question

1 Answer 1

up vote 0 down vote accepted
SELECT
  *
FROM 
  foos 
  INNER JOIN 
  (
    SELECT
      name
    FROM 
      foos 
    GROUP BY 
      name 
    HAVING 
      COUNT(*) > 1 
  ) AS dup
  ON foos.name = dup.name
ORDER BY 
  name
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.