Q: SQL to identify all records which have duplicates in a certain column with certain values We have data in DB2 something similar as below :




GUID
ID
Key
SubKey




ABC-123-DEF
1234567
20
1


ABC-123-DEF
1234567
22
1


ABC-123-DEF
1234567
21
2


ABC-123-DEF
1234568
22
1


ABC-124-DEF
1234667
21
2


ABC-124-DEF
1234668
22
2


ABC-125-DEF
1234767
21
1


ABC-125-DEF
1234768
22
1




I want to output with all details only where Subkey condition with 1 is repeating more than once , something as below




GUID
ID
Key
SubKey




ABC-123-DEF
1234567
20
1


ABC-123-DEF
1234567
22
1


ABC-123-DEF
1234567
21
2


ABC-123-DEF
1234568
22
1


ABC-125-DEF
1234767
21
1


ABC-125-DEF
1234768
22
1




Appreciate any help!

A: Choose the columns you want to group.
Assuming its the GUID column.
SELECT GUID
FROM THE_TABLE
WHERE SUBKEY = 1
GROUP BY GUID
HAVING COUNT(SUBKEY) > 1


A: You can use window aggregation group clause of SUM(..) OVER (PARTITION BY .. ) along with a conditional such as
SELECT GUID, ID, Key, SubKey
  FROM ( SELECT t.*,
                SUM(CASE WHEN SubKey = 1 THEN 1 END) OVER (PARTITION BY GUID) AS cnt
           FROM t ) tt
 WHERE cnt > 1 

Demo
