Find duplicate values only if separate column id differs-CodePudding

I have the following table:

id   item
1    A
2    A
3    B
4    C
3    H
1    E

I'm looking to obtain duplicate values from the id column only when the item column differs in value. The end result should be:

1 A
1 E
3 B
3 H

I've attempted:

select id, items, count(*)
from table
group by id, items
HAVING count(*) > 1

But this is giving only duplicate values from the id column and not taking into account the items column.

Any suggestions will be greatly appreciated.

CodePudding user response：

You can use a window function for this, this is generally far more efficient than using a self-join

SELECT
  t.id,
  t.items,
  t.count
from (
    SELECT *,
      COUNT(*) OVER (PARTITION BY t.id) AS count
    FROM YourTable t
) t
WHERE t.count > 1;