CODEWITHSUNDEEP
mysqlsql
Christopher Bales
Christopher Bales

Reputation: 1071

SQL: Select distinct from two columns where only column2 is different

I'm having trouble generating a query (and I've looked online and SO and can't find it). I'm wanting to select distinct two columns where only the second column is different.

Ideally, it would look like this:

SELECT DISTINCT colA, colB
FROM table
GROUP BY colA, colB

with the condition that colB is only different

For example, here's what I'd like it to do, in case what I said is not clear

colA | colB
-----------
abc  |1
abc  |2
abd  |1
abf  |1
xyz  |1
asd  |2

SQL MAGIC

calA | colB
-----------
abc  |1
abc  |2

It basically removes the rows where the colB is is different.

Thanks!

Upvotes: 1

Views: 233

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

To get the column A values with multiple B valuates, you can use a group by with a having clause:

SELECT colA
FROM table
GROUP BY colA
having min(colB) <> max(colB)

Then you need to join this back in to the original query, somehow. Here is an approach using in:

select *
from table
where colA in (SELECT colA
               FROM table
               GROUP BY colA
               having min(colB) <> max(colB)
              )

You can also replace the having comparison with having count(distinct colB) > 1, but I think the min()/max() comparison would generally perform better.

Upvotes: 1

Ed Gibbs
Ed Gibbs

Reputation: 26333

Based on your sample output, it looks like you're only including colA values if they have multiple, differing colB values. You can do that like this:

SELECT colA, colB FROM table
WHERE colA IN (
  SELECT colA
  FROM table
  GROUP BY colA
  HAVING COUNT(DISTINCT colB) > 1)

Upvotes: 3

Alvin Thompson
Alvin Thompson

Reputation: 5448

select colA, colB
from table
where colA in (select colA from table group by colA having count(*) > 1)
order by colA, colB

Upvotes: 0

Wrikken
Wrikken

Reputation: 70460

A JOIN solution is:

 SELECT DISTINCT a.*
 FROM table a
 JOIN table b
    ON  a.colA  = b.colA
    AND a.colB != b.colB

Upvotes: 4

Ross Smith II
Ross Smith II

Reputation: 12179

You could do

SELECT MIN(colA) AS colA, colB
FROM table
GROUP BY colB

or

SELECT MAX(colA) AS colA, colB
FROM table
GROUP BY colB

but your question is somewhat unclear, so I'm not sure this is what you are asking for, or not.

Upvotes: 1

Related Questions