CODEWITHSUNDEEP
sqlpostgresql
ApacheOne
ApacheOne

Reputation: 245

Find duplicate values only if separate column id differs

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.

Upvotes: 1

Views: 51

Answers (1)

Charlieface
Charlieface

Reputation: 71544

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;

db<>fiddle

Upvotes: 2

Related Questions