CODEWITHSUNDEEP
mysql
Sangram Anand
Sangram Anand

Reputation: 10650

My sql query to retrieve only duplicate records

I have a table named 'entity' in DB which consists of

id, entityname, created time, modified time.

I want to retrieve only records which have duplicate entries like if there are two entities with name Arthur then i want only that two records. I couldn't construct the query as comparing entityname with entity name is returning me all the records.

Upvotes: 0

Views: 117

Answers (2)

BizApps
BizApps

Reputation: 6130

This will select all entityName and RecordCount

 Select entityName, count(*) as RecordCount
 from entity
 group by entityName

where unique entityName RecordCount = 1

Then to select all records with duplicates:

 Select entityName, count(*) as  RecordCount
 from entity
 group by entityName
 having count(*) > 1

Upvotes: 0

naresh
naresh

Reputation: 2113

SELECT * 
FROM entity 
WHERE entityname IN 
(SELECT entityname 
 FROM entity 
 GROUP BY entityname 
 HAVING COUNT(*) > 1)

Upvotes: 1

Related Questions