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.
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 ) 
SELECT s.* FROM StyleTable s INNER JOIN (SELECT Color FROM StyleTable GROUP BY Color HAVING COUNT(*) > 1) q ON s.Color = q.Color 
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 
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
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');