Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE persons (serialno, given, family, dob, gender, address) AS
SELECT '001', 'Alice', 'Abbot', DATE '1970-01-01', 'Female', 'Street1' FROM DUAL UNION ALL
SELECT '002', 'Alice', 'Abbot', DATE '1970-01-01', 'Female', 'Street2' FROM DUAL UNION ALL
SELECT '003', 'Alice', 'Abbot', DATE '1970-01-01', 'Female', 'Street2' FROM DUAL UNION ALL
SELECT '004', 'Betty', 'Baron', DATE '1981-01-01', 'Female', 'Street3' FROM DUAL UNION ALL
SELECT '005', 'Carol', 'Count', DATE '1956-01-01', 'Female', 'Street4' FROM DUAL UNION ALL
SELECT '006', 'Diana', 'Duke',  DATE '1992-01-01', 'Female', 'Street5' FROM DUAL;
6 rows affected

SELECT serialno, given, family, dob, gender, address
FROM   (
  SELECT serialno, given, family, dob, gender, address,
         COUNT(*)  OVER (PARTITION BY given, family, gender) AS num_matches
  FROM   persons
)
WHERE num_matches > 1;


SERIALNO
GIVEN
FAMILY
DOB
GENDER
ADDRESS


001
Alice
Abbot
01-JAN-70
Female
Street1

002
Alice
Abbot
01-JAN-70
Female
Street2

003
Alice
Abbot
01-JAN-70
Female
Street2


SELECT serialno, given, family, dob, gender, address,
       min_serialno, min_dob, min_address
FROM   (
  SELECT serialno,
         given,
         family,
         dob,
         gender,
         address,
         MIN(serialno) OVER (PARTITION BY given, family, gender) AS min_serialno,
         MIN(dob) KEEP (DENSE_RANK FIRST ORDER BY serialno)
           OVER (PARTITION BY given, family, gender) AS min_dob,
         MIN(address) KEEP (DENSE_RANK FIRST ORDER BY serialno)
           OVER (PARTITION BY given, family, gender) AS min_address
  FROM   persons
)
WHERE serialno > min_serialno;


SERIALNO
GIVEN
FAMILY
DOB
GENDER
ADDRESS
MIN_SERIALNO
MIN_DOB
MIN_ADDRESS


002
Alice
Abbot
01-JAN-70
Female
Street2
001
01-JAN-70
Street1

003
Alice
Abbot
01-JAN-70
Female
Street2
001
01-JAN-70
Street1


SELECT serialno, given, family, dob, gender, address,
       PRIOR serialno AS p_serialno,
       PRIOR dob      AS p_dob,
       PRIOR address  AS p_address
FROM   persons
WHERE  LEVEL = 2
CONNECT BY
       PRIOR gender   = gender
AND    PRIOR given    = given
AND    PRIOR family   = family
AND    PRIOR serialno < serialno


SERIALNO
GIVEN
FAMILY
DOB
GENDER
ADDRESS
P_SERIALNO
P_DOB
P_ADDRESS


002
Alice
Abbot
01-JAN-70
Female
Street2
001
01-JAN-70
Street1

003
Alice
Abbot
01-JAN-70
Female
Street2
001
01-JAN-70
Street1

003
Alice
Abbot
01-JAN-70
Female
Street2
002
01-JAN-70
Street2
SERIALNO	GIVEN	FAMILY	DOB	GENDER	ADDRESS
001	Alice	Abbot	01-JAN-70	Female	Street1
002	Alice	Abbot	01-JAN-70	Female	Street2
003	Alice	Abbot	01-JAN-70	Female	Street2