Return all duplicate rows

Question

I've written this code to find duplicates and it works fine:

SELECT *
FROM StyleTable 
GROUP BY Color
HAVING count(*) > 1 

The problem is, it's returning just one of the duplicate rows. Is it possible to return all the duplicate rows? I'm guessing it may have something to do with the 'GROUP BY' but I'm not sure how to change it. I don't want to delete the values, just return them.

Answer 1

You have to join back to the table again to get the duplicates I think. Something like:

SELECT * 
FROM StyleTable 
WHERE Color IN (
  SELECT Color  
  FROM StyleTable   
  GROUP BY Color  
  HAVING count(*) > 1 
)     

Answer 2

SELECT s.*
    FROM StyleTable s
        INNER JOIN (SELECT Color
                        FROM StyleTable
                        GROUP BY Color
                        HAVING COUNT(*) > 1) q
            ON s.Color = q.Color