Link to home
Get AccessLog in
Avatar of DaveChoiceTech
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
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

What database you are working with?
Please improve your sample data by showing duplicates. And, also show sample output.
Avatar of DaveChoiceTech
DaveChoiceTech

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.
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
ASKER CERTIFIED SOLUTION
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of PortletPaul
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