Trouble Finding Twins

[mccarthyxfer]

I have a table that contains parent information along with data for each of the parent's dependent children. I need to find only those children that are twins (i.e., have the same birthday). Simplified version below:

Rec_No Par_ID Ch_ID Ch_Seq Ch_DOB
1...........1234......4321......1.......10/15/1988
2...........1234......4322......2.......6/12/1990
3...........1234......4323......3.......6/12/1990
4...........2345......5432......1.......4/20/1993
5...........2345......5432......1.......4/20/1993
6...........3456......6543......1.......2/10/1985
7...........3456......6544......2.......4/15/1987
8...........3456......6545......3.......8/12/1990
9...........3456......6546......4.......10/15/1988

In this case I would only want to have Rec_No's 2 and 3 returned - distinct children who share a birthday. While records 4 and 5 have the same DOB, they are referring to the same dependent child. Likewise, even though records 1 and 9 have the same DOB, they refer to different children altogether.

I've tried GROUPing, HAVING, COUNT. I've tried joining the table to itself but it seems that I just can't get the results I need. Any help would be greatly appreciated.

 

[gmmastros]

I've tried GROUPing, HAVING, COUNT. I've tried joining the table to itself but it seems that I just can't get the results I need.

Did you try a combination of these? I did.

Select	*
From    YourTableNameHere T
        Inner Join (
          Select Par_ID, Ch_DOB
          From   YourTableNameHere
          Group BY Par_ID, Ch_DOB
          Having Count([!]Distinct Ch_ID[/!]) > 1
          ) As Twins
          On T.Par_ID = Twins.Par_ID
          And T.Ch_DOB = Twins.Ch_DOB

Notice the Count(Distinct Ch_ID). This allows use to filter out rows that are referring to the same child (records 4 and 5 in your sample data).

Notice the Count(Distinct Ch_ID) > 1. The greater than 1 will also return triplets, quadruplets, etc... if you ONLY want twins, change this to " = 2 ".

** Note, I originally tried a simple self join, but it failed for triplets. It returned duplicate rows.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Here is alternative solution using SQL 2005+ and above. You may want to compare the execution plans:

;with cte as (select *, count(*) over (partition by Par_ID, Ch_Dob) as PossibleDups, 
count(*) over (partition by Par_ID, Ch_Id, Ch_Dob) as Dups
from YourTable)

select * from cte where PossibleDups > 1 and PossibleDups > Dups

PluralSight Learning Library