SQL to identify all records which have duplicates in a certain column with certain values-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

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 ELSE 0 END) 
                    OVER (PARTITION BY GUID) AS cnt
           FROM t ) tt
 WHERE cnt > 1

Demo