How to group by when querying duplicate records?

Posted on

Question :

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.

Answer :

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

Leave a Reply

Your email address will not be published.