CODEWITHSUNDEEP
sqldb2
user1613270
user1613270

Reputation: 533

DB2 sql group by/count distinct column values

if I have a table with values like this:

ID  SUBID    FLAG
-----------------
 1      1       1
 1      2  (null)
 2      3       1
 2      3  (null)
 3      4       1
 4      5       1
 4      6  (null)
 5      7       0
 6      8  (null)
 7      9       1

and I would like to get all the ID's where 'FLAG' is only set to 1, so in this case the query would return

ID  SUBID    FLAG
-----------------
 3      4       1
 7      9       1

How can I achieve this?

Upvotes: 2

Views: 2686

Answers (2)

Taryn
Taryn

Reputation: 247630

I don't have a db2 instance to test on but this might work:

select t1.id, t1.subid, t1.flag
from yourtable t1
inner join
(
  select id 
  from yourtable 
  group by id
  having count(id) = 1
) t2
  on t1.id = t2.id
where t1.flag = 1;

Upvotes: 1

Diego
Diego

Reputation: 36126

try this:

SELECT * FROM flags where flag=1
and ID NOT in( SELECT ID FROM flags where flag !=1 OR flag IS NULL)

Upvotes: 2

Related Questions