Search code examples
sqldb2

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!

Solution

  • 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