Clever Geek Handbook

SQL lists all rows where column A appears multiple times but has different values ​​for column B

I have two columns, for example, I want to list all rows where items in ROW A appear more than once in column A, but have different values ​​in column B. So far I have not figured out the answer

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


source to share


2 answers


Try this query:

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


An alternative suggestion HAVING

that might be more effective:

HAVING MIN(ColumnB) <> MAX(ColumnB)
+3


source


Try this query:

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


  • The first predicate in an expression HAVING

    evaluates to true if the value ColumnA

    appears more than once.
  • The second predicate is true if all values ​​in ColumnB

    are different within the group ColumnA

    .

Demo SQL Fiddle

0


source






More articles:


All Articles