DaveChoiceTech
asked on
find duplicate column within a group
The table below shows 12 records grouped by ID1, ID2 and ID3
The first and second group contain duplicate ID3's within the group.
How can I write a query to find these duplicates.
ID1 ID2 ID3
1 100 A
1 101 A
1 102 B
1 103 C
2 104 A
2 105 B
2 106 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
The first and second group contain duplicate ID3's within the group.
How can I write a query to find these duplicates.
ID1 ID2 ID3
1 100 A
1 101 A
1 102 B
1 103 C
2 104 A
2 105 B
2 106 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
ASKER
DB2
oh, there are three columns, it seemed two column at first glance:
ID1 ID2 ID3
1 100 A
1 101 A
1 102 B
1 103 C
2 104 A
2 105 B
2 106 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
The values in bold are duplicates and you want to have:
ID1 ID2 ID3
1 100 A
1 102 B
1 103 C
2 104 A
2 105 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
You want distinct ID1_D3 combination. This was not clearly stated. And it is still a guess work.
ID1 ID2 ID3
1 100 A
1 101 A
1 102 B
1 103 C
2 104 A
2 105 B
2 106 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
The values in bold are duplicates and you want to have:
ID1 ID2 ID3
1 100 A
1 102 B
1 103 C
2 104 A
2 105 B
2 107 C
3 108 A
3 109 B
3 110 C
3 111 D
You want distinct ID1_D3 combination. This was not clearly stated. And it is still a guess work.
ASKER
My apologies for the lack of clarity to my question. Here is the sample output I am looking for
ID1 ID2 ID3
1 100 A
1 101 A
2 105 B
2 106 B
ID1 ID2 ID3
1 100 A
1 101 A
2 105 B
2 106 B
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
tiny suggestions, no points please
Select ID1, ID2, ID3
From(Select ID1, ID2, ID3,
Count(ID3) Over(Partition by ID1, ID3 Order By ID2) cnt
From #T
) As D
Where cnt > 1
Order By ID1, ID2
Select ID1, ID2, ID3
From(Select ID1, ID2, ID3,
Count(ID3) Over(Partition by ID1, ID3 Order By ID2) cnt
From #T
) As D
Where cnt > 1
Order By ID1, ID2
Please improve your sample data by showing duplicates. And, also show sample output.