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 |