SQL Server 2017

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

select @@version;


(No column name)


Microsoft SQL Server 2017 (RTM-CU30) (KB5013756) - 14.0.3451.2 (X64) 
 Jun 22 2022 18:20:15 
 Copyright (C) 2017 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table my_table (objid varchar(2), firstname varchar(20), lastname varchar(20), active varchar(2));

insert into my_table (objid, firstname, lastname, active) VALUES ('1', 'test', 'test', '0');
insert into my_table (objid, firstname, lastname, active) VALUES ('2', 'test', 'test', '1');
insert into my_table (objid, firstname, lastname, active) VALUES ('3', 'test1', 'test1', '1');
insert into my_table (objid, firstname, lastname, active) VALUES ('4', 'test2', 'test2', '0');
insert into my_table (objid, firstname, lastname, active) VALUES ('5', 'test2', 'test2', '0');
insert into my_table (objid, firstname, lastname, active) VALUES ('6', 'test3', 'test3', '1');
6 rows affected

select * from my_table


objid
firstname
lastname
active


1
test
test
0

2
test
test
1

3
test1
test1
1

4
test2
test2
0

5
test2
test2
0

6
test3
test3
1


select t.objid, t.firstname, t.lastname, t.active
from
(
select t.*, count(*) over (partition by firstname, lastname) as cnt
from my_table t
) t
where t.cnt > 1;


objid
firstname
lastname
active


1
test
test
0

2
test
test
1

4
test2
test2
0

5
test2
test2
0

objid	firstname	lastname	active
1	test	test	0
2	test	test	1
3	test1	test1	1
4	test2	test2	0
5	test2	test2	0
6	test3	test3	1