CODEWITHSUNDEEP
sqlduplicates
Marin
Marin

Reputation: 12910

Finding Duplicate names sql

SELECT COUNT(organization.ID) 
FROM organization 
WHERE name in ( SELECT name FROM organization GROUP BY name HAVING count( name ) >1 )
AND organization.APPROVED=0 
AND organization.CREATED_AT>'2010-07-30 10:30:21'

I'm trying to find duplicates, but this query is taking a very long time roughly 5-6 seconds. Is there another way I can find duplicates without using my method? Thanks.

SubQuery: 0.28 seconds. Everything 5.98 seconds.

Upvotes: 2

Views: 5062

Answers (3)

tvm
tvm

Reputation: 3449

SELECT organization.name, COUNT(organization.ID)
  FROM organization
 WHERE organization.APPROVED=0
   AND organization.CREATED_AT>'2010-07-30 10:30:21'
 GROUP BY name
HAVING count(organization.id) > 1;

Upvotes: 1

Gerald Versluis
Gerald Versluis

Reputation: 34013

Why not just do it like:

SELECT COUNT(organization.ID)
FROM organization 
WHERE organization.APPROVED=0 
AND organization.CREATED_AT>'2010-07-30 10:30:21'
GROUP BY organization.name
HAVING count(organization.name) > 1;

Upvotes: 0

Taryn
Taryn

Reputation: 247710

There is no need to use the query in the WHERE clause. You can use a GROUP BY and a HAVING clause to accomplish this:

SELECT COUNT(o.ID) 
FROM organization o
WHERE o.APPROVED=0 
   AND o.CREATED_AT>'2010-07-30 10:30:21'
GROUP BY o.Name
HAVING COUNT(o.name) > 1

Upvotes: 0

Related Questions