SQL list all rows where column A appears more than once but have distinct Column B values

Question

I have two columns for example, I want to list all the rows where the items in ROW A appears more than once in Column A, but have distinct values in Column B. So far I have not figured out an answer

Column A     Column B
Apple         x
Banana        a
Orange        b
Orange        b
Banana        x
Avocado       d

Answer 1

Try this query:

SELECT ColumnA
FROM table t
GROUP BY ColumnA
HAVING COUNT(DISTINCT ColumnB) >= 2;

An alternative HAVING clause that might be more efficient is:

HAVING MIN(ColumnB) <> MAX(ColumnB)

Answer 2

Try this query:

SELECT ColumnA
FROM mytable 
GROUP BY ColumnA
HAVING COUNT(*) > 1 AND COUNT(DISTINCT ColumnB) = COUNT(*)

• First predicate in HAVING clause is true if ColumnA value appears more than once.
• Second predicate is true if all values in ColumnB are distinct within a ColumnA group.

SQL Fiddle Demo