CODEWITHSUNDEEP
sqlsqliteselectgroup-byduplicates
XSL
XSL

Reputation: 3055

Return all duplicate rows

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.

Upvotes: 17

Views: 16877

Answers (4)

surfmuggle
surfmuggle

Reputation: 5942

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

Table Duplicate with repeated Name values for given id

only chocolate and cherry are unique for a given Id.

Take a look at the demo which should looks like this

result of query

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');

Upvotes: 0

Suneel Kumar
Suneel Kumar

Reputation: 11

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

Upvotes: 1

Peter Wishart
Peter Wishart

Reputation: 12270

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 
)

Upvotes: 27

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

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

Upvotes: 7

Related Questions