get index column of duplicate rows based on multiple columns

Question

I need to get ID and person name from the below table where address1, address2, state, city, country columns have same data for all the roles.

ID	Name	Address1	Address2	State	City
1	AB	AB@123	AB@345	st1	ct1
2	AB	AB@123	AB@345	st1	ct1
3	CD	AB@123	AB@345	st1	ct1
4	EF	EF@123	AB@345	st1	ct1

I want output as

ID	Name
1	AB
3	CD

select ID, Name, count(*) from person group by address1,address2,city,state having count(*) > 1;

which is giving the exception : not a group by expression.

Please suggest a better approach to achieve about output.

Answer 1

You can use analytic functions:

SELECT ID, Name, Address1, Address2, State, City
FROM   (
  SELECT p.*,
         COUNT(DISTINCT Name) OVER (PARTITION BY Address1, Address2, State, City)
           AS cnt,
         ROW_NUMBER() OVER (
           PARTITION BY Address1, Address2, State, City, Name
           ORDER BY id
         ) AS rn
  FROM   person p
)
WHERE  cnt > 1
AND    rn = 1;

Which, for the sample data:

CREATE TABLE person (ID, Name, Address1, Address2, State, City) AS
SELECT 1, 'AB', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 2, 'AB', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 3, 'CD', 'AB@123', 'AB@345', 'st1', 'ct1' FROM DUAL UNION ALL
SELECT 4, 'EF', 'EF@123', 'AB@345', 'st1', 'ct1' FROM DUAL;

Outputs:

ID	NAME	ADDRESS1	ADDRESS2	STATE	CITY
1	AB	AB@123	AB@345	st1	ct1
3	CD	AB@123	AB@345	st1	ct1

db<>fiddle here