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.

Here 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 itself to different APPLICATIONNAME.

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

How to do an Oracle query for such a scenario?

Possible duplicate of [How do I find duplicate values in a table in Oracle?](https://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle) — Travis J, Aug 15 '17 at 18:35

Juan · Accepted Answer · 2017-08-15T18:27:36.437

I didn't quite understand which of these two you would need so I included both:

A query to find duplicates:

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

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

select distinct localid, applicationname 
from yourtable

EDIT I

I think I now understood. This query will give you the localids that are allocated to more than one applicationname.

select localid, count(*)
from (
    select distinct localid, applicationname 
    from yourtable
) 
group by localid
having count(*) > 1;

1 Answers1