Find duplicate items in a column Oracle SQL

Question

I have searched for Find duplicate entries in a column and Oracle: find duplicate rows in select query, but couldn't seem to get any answer...

I have some data that looks like this

columnA    columnB    columnC
1111111    emailID1   true
1111111    emailID2   false
1111222    emailID3   true
1111339    emailID4   true
2384398    emailID5   true

I would like to only display these column that has the same values in columnA but can be different in columnB and/or C:

columnA    columnB    columnC
1111111    emailID1   true
1111111    emailID2   false

Using the having >1 doesn't really seem to capture this, any ideas? Thanks.

Answer 1

Using having count(*) > 1 is just one half of the puzzle. The other half is getting the corresponding rows.

You can do it like this:

SELECT *
FROM MyTable
WHERE ColumnA IN (
    SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1
)

Answer 2

Try this:

SELECT t.*
FROM (SELECT ColumnA FROM MyTable GROUP BY ColumnA HAVING COUNT(*) > 1) dups
JOIN MyTable t ON t.ColumnA = dups.ColumnA

This will scale well too, as long as an index is on ColumnA:

create index MyTable_ColumnA on MyTable(ColumnA);

such an index would be used for both the main query and the inner query, giving you very good performance.

Answer 3

I usually like to avoid hitting the table more than once in the query - this will work well even without an index - doing only one scan over the table:

SELECT columnA, columnB, columnC
FROM (SELECT mytable.*
            ,COUNT(*) OVER (PARTITION BY columnA) countA
      FROM mytable)
WHERE countA > 1