CODEWITHSUNDEEP
mysqlsql
Steve Taylor
Steve Taylor

Reputation: 1951

SQL to select distinct values from one column where another column has varying values

I have a huge amount of data. To explain the issue, consider this ultra-minimal set of data:

id     col1    col2
-------------------
1      ab      12
2      ab      12
3      ab      12
4      cd      34
5      cd      43
6      ef      34
7      ef      56
8      ef      34

What I need is to select all distinct values in col1 where there is more than one value in col2. So the results from the above would be something like this:

col1
----
cd
ef

Or even better, a row for each unique corresponding value in col2:

col1    col2
------------
ab      12
cd      34
cd      43
ef      34
ef      56

Upvotes: 0

Views: 1311

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can do this with a group by and having:

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

If you just want the distinct values, use select distinct:

select distinct col1, col2
from t;

Upvotes: 1

jarlh
jarlh

Reputation: 44776

First one, return col1 values having at least two different col2 values:

select col1 from
tablename
group by col1
having count(distinct col2) >= 2

Second, return col1 with col2, when col1 has at least two different col2 values:

select * from tablename
where col1 in (
    select col1 from
    tablename
    group by col1
    having count(distinct col2) >= 2)

Upvotes: 1

Related Questions