Need to select ALL columns while using COUNT/Group By

Question

Ok so I have a table in which ONE of the columns have a FEW REPEATING records.

My task is to select the REPEATING records with all attributes.

CustID FN LN DOB City State

the DOB has some repeating values which I need to select from the whole table and list all columns of all records that are same within the DOB field..

My try...

Select  DOB, COUNT(DOB) As 'SameDOB' from Table1

group by DOB

HAVING (COUNT(DOB) > 1)

This only returns two columns and one row 1st column is the DOB column that occurs more than once and the 2nd column gives count on how many.

I need to figure out a way to list all attributes not just these two...

Please guide me in the right direction.

Answer 1

I think a more general solution is to use windows functions:

select *
from (select *, count(*) over (partition by dob) as NumDOB
      from table
     ) t
where numDOB > 1

The reason this is more general is because it is easy to change to duplicates across two or more columns.

Answer 2

Select * 
FROM  Table1 T
WHERE T.DOB IN( Select   I.DOB
                FROM     Table1 I
                GROUP BY I.DOB
                HAVING   COUNT(I.DOB) > 1)