Ignoring records for certain criteria

Question

I have data as below,

ACCOUNT	FLAG
asdf	1
asdf	2
asdf	3
kjhj	1
qwer	1
qwer	1

need to get output:

ACCOUNT	FLAG
kjhj	1
qwer	1

situation is that need to get records that have only "1" in 2nd column. If they have any other value other than "1", need to ignore all records for particular 1st column. can you plz suggest a query

tried group by but didn't find option

Answer 1

Group to a single account per output row, then assert that all rows in a group must have flag=1 by using HAVING with both min and max.

SELECT
  account,
  MIN(flag)   flag
FROM
  your_table
GROUP BY
  account
HAVING
      MIN(flag) = 1
  AND MAX(flag) = 1

Some people prefer the following and being more understandable, and it also causes a NULL row to exclude the group...

HAVING
  MIN(CASE WHEN flag=1 THEN 1 ELSE 0 END) = 1