Filter in SQL on distinct values after grouping

Question

I have a dataset like

col1       col2       col3
A          x          1
A          x          2
A          x          3
B          y          4
B          -y         5
B          y          6
C          -z         7
C          z          8
C          -z         9
D          t          10
D          t          11
D          t          12

how can i pick out just the groups from col1 that have distinct values in col2? So A,D in this case.

something like

select * from table t1
where (select count(distinct col2)
       from table t2
       where t1.col1 = t2.col1) > 1

but more optimized?

Answer 1

If all you need is the column col1 you can group by col1 and set the condition in the HAVING clause:

SELECT col1
FROM tablename
GROUP BY col1
HAVING COUNT(DISTINCT col2) = 1;

If you want all the rows from the table use the above query with the operator IN:

SELECT *
FROM tablename 
WHERE col1 IN (
  SELECT col1
  FROM tablename
  GROUP BY col1
  HAVING COUNT(DISTINCT col2) = 1
)