Search code examples
sqlms-accesscombinationsdistinct-values

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

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
Solution

  • 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)