MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `person` (
  `id` int not null auto_increment primary key,
  `firstname` VARCHAR(32),
  `lastname` VARCHAR(32),
  `address` VARCHAR(128)
);

INSERT INTO `person`
  (`firstname`, `lastname`, `address`)
VALUES
  ('Jim', 'Jones', '100 MAIN ST'),
  ('Jim', 'Smith', '100 MAIN ST'),
  ('John', 'Smith', '100 MAIN ST'),
  ('Alex', 'Smith', '23 Wall St'),
  ('Fox', 'Smith', '23 Wall St'),
  ('John', 'Smith', '100 main St');
SELECT id
,      firstname
,      lastname
,      address
FROM person p
WHERE id > (
    SELECT min(id) FROM person p1 
    WHERE p1.address = p.address
    GROUP BY p1.address
    HAVING count(*) > 1
)


id
firstname
lastname
address


2
Jim
Smith
100 MAIN ST

3
John
Smith
100 MAIN ST

5
Fox
Smith
23 Wall St

6
John
Smith
100 main St


select * from person
where (firstname
,      lastname
,      address) in (

select firstname
,      lastname
,      address from person
group by firstname
,      lastname
,      address having count(*)>1
)


id
firstname
lastname
address


3
John
Smith
100 MAIN ST

6
John
Smith
100 main St

id	firstname	lastname	address
2	Jim	Smith	100 MAIN ST
3	John	Smith	100 MAIN ST
5	Fox	Smith	23 Wall St
6	John	Smith	100 main St