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.

Landscapist

I think this SQL wouldn't run, unless Color is the only column in the STyleTable table. – Sculpt

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 
)     

Prettypretty

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

Kinsella

Answer 3

WITH cte AS ( 
   SELECT FirstName, LastName, COUNT(*) occurrences 
   FROM Customer 
   GROUP BY FirstName, LastName HAVING COUNT(*) > 1
)
SELECT * FROM Customer INNER JOIN cte 
         ON cte.FirstName = Customer.FirstName 
         AND cte.LastName = Customer.LastName
ORDER BY Customer.FirstName, Customer.LastName

Consciencestricken

If you don't mind its usually helpful to give a brief explaination of you answer and its uses over other answers – Blavatsky

Answer 4

I had a similar problem and want to provide an example with sample records.

WITH CTE AS (
    SELECT Id, Name, Price FROM Duplicates
)
SELECT CTE.Id, CTE.Name, CTE.Price FROM CTE 
         INNER JOIN (SELECT Id, Name 
                       FROM Duplicates 
                       Group BY Id, Name 
                       HAVING Count(*)>1
         ) as sq
         ON CTE.Id = sq.Id 
            AND CTE.Name = sq.Name

In the table Duplicates are multiple rows with Bread and Butter within the same Id

only chocolate and cherry are unique for a given Id.

Take a look at the demo which should looks like this

SQL statements to recreate the sample

Create the table

CREATE TABLE Duplicates 
(
  Id INTEGER,
  Name TEXT,
  Price REAL
);

Insert some records

INSERT INTO Duplicates (Id, Name, Price) VALUES (1, 'Bread', '1.01');
INSERT INTO Duplicates (Id, Name, Price) VALUES (1, 'Chocolate', '3.03');
INSERT INTO Duplicates (Id, Name, Price) VALUES (1, 'Bread', '10.20');
INSERT INTO Duplicates (Id, Name, Price) VALUES (2, 'Butter', '2.02');
INSERT INTO Duplicates (Id, Name, Price) VALUES (2, 'Cherry', '7.03');
INSERT INTO Duplicates (Id, Name, Price) VALUES (2, 'Butter', '20.20');
INSERT INTO Duplicates (Id, Name, Price) VALUES (3, 'Bread', '30.01');
INSERT INTO Duplicates (Id, Name, Price) VALUES (3, 'Butter', '30.02');
INSERT INTO Duplicates (Id, Name, Price) VALUES (3, 'Cherry', '30.03');

Obola