Find duplicate values in an Oracle table

Question

Have a table in an Oracle database called MESSAGE. This table has some fields among them LOCALID and APPLICATIONNAME.

Below are examples of values entered in the database

LOCALID - APPLICATIONNAME
1       - app1
1       - app1
1       - app1
2       - app2
2       - app2
2       - app2
1       - app3
1       - app3

At certain times LOCALID is repeating for different APPLICATIONNAME.

I need to find all these values that have different APPLICATIONNAME and LOCALID alike.

How to query Oracle for such a scenario?

Answer 1

I didn’t quite understand which of these two you’d need, so I included both:

A query to find duplicates:

Select localid, applicationname, count(*)
from mensagem
group by localid, applicationname
having count(*) > 1;

A query to find all the distinct combinations or the two fields.

select distinct localid, applicationname 
from mensagem