How to select rows where the value in one column doesn't have any duplicates (i.e DISTINCT) AND the value in a different column does, IN SQL?

Question

This is in DB2. I want to write query that results in rows where values in one specified column are DISTINCT but then ALSO values in a second column are NOT distinct.

Here's an example dataset:

COL1     COL2          COL3
149999      Y          34567
149999      N          34567
148888      N          34567
147777      N          45678
156666      Y          56789
156666      N          56789
155555      N          56789

---

Let's say COL1 is the column i want to be distinct and COL3 is the one that I want to have duplicates.

The query I need would then give the following result:

COL1        COL2         COL3
148888      N            34567
155555      N            56789

---

Because COL1 is unique in these rows but COL3 is not

Sorry, if I'm explaining this poorly, I'm kind of a beginner with SQL/DB2

Answer 1

Window functions are one approach:

select t.*
from (select t.*,
             count(*) over (partition by col1) as cnt_col1,
             count(*) over (partition by col3) as cnt_col3
      from t
     ) t
where cnt_col1 = 1 and cnt_col3 > 1;

Answer 2

If for some reason you don't want to use window functions.

SELECT * 
  FROM TABLE_1
 WHERE EXISTS -- unique field
         ( SELECT 1
             FROM TABLE_1 T2
            WHERE T1.Field2 = T2.Field2
            GROUP
               BY T2.Field2
           HAVING COUNT(1) = 1
         )
   AND EXISTS -- nonunique field
         ( SELECT 1
             FROM TABLE_1 T3
            WHERE T1.Field3 = T3.Field3
            GROUP
               BY T3.Field3
           HAVING COUNT(1) > 1
         )