CODEWITHSUNDEEP
mysqlsql
user3264565
user3264565

Reputation: 61

Finding duplicates by size and name using sql query

I have a table "MyDuplicateTable" which has all the file entries(filepath,filesize,crc32,filename).Now I want to find out the duplicates by size and filename.I have tried the below query for that,but it gives me only one result. Eg:

 MyDuplicateTable:
    Filepath           Filesize CRC32 Filename
    c:\dup\a.txt          0       0     a.txt
    c:\dup\dup1\a.txt     0       0     a.txt
    c:\dup\b.txt          0       0     b.txt

Now I want to find duplicates by filename and size.Below is my query.

 Query:"SELECT FILEPATH FROM MYDUPLICATETABLE GROUP BY FILENAME, SIZE HAVING COUNT(*)>1" Result of query:But above query returns only one record[c:\dup\a.txt]

Another query

"Select Filepath from MyDuplicateTable where Filename IN(select Filename from MyDuplicateTable GROUP BY Filename,Size HAVING COUNT(*)>1)" Result of query:Above query doesnt return anything :(

Thanks for your help in advance.

Upvotes: 0

Views: 48

Answers (2)

Scott
Scott

Reputation: 19

Select Filepath from MyDuplicateTable where Filename IN(select Filename from MyDuplicateTable GROUP BY Filename,Size HAVING COUNT(Filename)>1)"

Try - count(Filename) in your having clause as above instead of count(*).

Upvotes: 0

xdazz
xdazz

Reputation: 160903

Use a join to get the result.

SELECT t1.Filepath 
FORM MyDuplicateTable t1
INNER JOIN (
  SELECT Filesize, Filename 
  FROM MyDuplicateTable 
  GROUP BY FILENAME, Filesize 
  HAVING COUNT(*) > 1
) t2 ON t1.Filesize = t2.Filesize AND t1.Filename = t2.Filename

Upvotes: 0

Related Questions