Search code examples
mysqldomo

MySQL - Compare rows within group

Using MySQL, I need to compare rows within the same group and return rows that don't match according to a particular field.

For example, say I have the following table:

cust note
1 foo0
2 foo1
2 foo2
3 foo3
3 foo3
3 foo4

I need to return all rows that have the same id, but have differing note values. The resulting output for the above table should be:

cust note
2 foo1
2 foo2
3 foo3
3 foo4

How would I go about accomplishing this goal?

Thanks in advance!

Solution

  • you can use a subquery for this

    SELECT DISTINCT `cust`, `note` FROM tabl2 WHERE cust IN (SELECT Cust FROM tabl2  GROUP BY cust HAVING COUNT(DISTINCT note) > 1)

    cust | note
---: | :---
   2 | foo1
   2 | foo2
   3 | foo3
   3 | foo4

    db<>fiddle here