CODEWITHSUNDEEP
sqlgroup-bydistinct
Raksha
Raksha

Reputation: 1699

Filter in SQL on distinct values after grouping

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?

Upvotes: 0

Views: 2256

Answers (2)

forpas
forpas

Reputation: 164099

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
)

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269883

You can use group by and having:

select col1
from t
group by col1
having min(col2) <> max(col2);

Upvotes: 2

Related Questions