SQL query to find duplicates

Question

I am trying to write a query in SQL server to find out if there are any multiple rows for each hash value.
I need all filenames where the hash value has duplicates.

The result should be (based on my example below)

003B4C68BC143B0290E04432A3A96092    File0003.jpg
003B4C68BC143B0290E04432A3A96092    File0004.jpg
003B4C68BC143B0290E04432A3A96092    File0005.jpg

Please let me know.

Here is the table structure

File table
-----------------------------------------
hash          FileName
---------------------------------------
000341A486F5492877D588BED0806650    File0001.jpg
00363EF2ECEEA32F10176EB64A50283F    File0002.jpg
003B4C68BC143B0290E04432A3A96092    File0003.jpg
003B4C68BC143B0290E04432A3A96092    File0004.jpg
003B4C68BC143B0290E04432A3A96092    File0005.jpg

Answer 1

select * 
from File 
where hash in (select 
               hash 
               from File
               group by hash
               having count(*) > 1)

Answer 2

You can use EXISTS to check for duplicates,

SELECT  a.*
FROM    TableName a
WHERE   EXISTS
        (
            SELECT  1
            FROM    Tablename b
            WHERE   a.hash = b.hash
            GROUP   BY hash
            HAVING  COUNT(*) > 1
        )

• SQLFiddle Demo

or INNER JOIN

SELECT  a.*
FROM    [File] a
        INNER JOIN
        (
            SELECT  hash
            FROM    [File] b
            GROUP   BY hash
            HAVING  COUNT(*) > 1
        ) b ON  a.hash = b.hash

• SQLFiddle Demo