find duplicate column within a group

DaveChoiceTech
DaveChoiceTech used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

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

Author

Commented:
DB2
Mike EghtebasDatabase and Application Developer

Commented:
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.
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

Author

Commented:
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
Database and Application Developer
Commented:
This works in SQL Server, I hope it will work in DB2 as well:
Select ID1, ID2, ID3
From(Select ID1, ID2, ID3,
     Count(ID3) Over(Partition by ID1, ID3 Order By ID1) cnt
From #T
) As D
Where cnt > 1

Open in new window

Replace #T with your table name. Here is the temp table I used:
Create table #t(ID1 int, ID2 int, ID3 char(1));
Insert into #t(ID1, ID2, ID3) Values
(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');
select * from #t;

Open in new window


Modified...  changed Where cnt=2 to  Where cnt > 1
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial