Detect duplicated registries in Oracle

Question

Hi,

I have a table in Oracle and I want to select all the duplicated values, I mean: I can detect that a register is duplicated because some column contains the same value:

ColumnA  ColumnB
1        A
2        A
3        A
4        B

I want to get something like:

1 A
1 A
1 A

Thanks a lot.

Answer 1

Assuming you want all rows from the original table that have some duplicates in one column (ColumnB), you can use the following query:

SELECT ColumnA, ColumnB FROM Table
WHERE ColumnB IN (
    SELECT ColumnB FROM Table
    GROUP BY ColumnB HAVING COUNT(*) > 1 
)

For your original data, that would return:

ColumnA ColumnB
1       A
2       A
3       A

If it's enough to get all values that are used multiple times in the table, you can use a simpler query:

SELECT ColumnB FROM Table
GROUP BY ColumnB HAVING COUNT(*) > 1 

This would return just one row with A.