By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable(
id INTEGER NOT NULL PRIMARY KEY
,type VARCHAR(6) NOT NULL
,last_name VARCHAR(11) NOT NULL
,first_name VARCHAR(12) NOT NULL
);
INSERT INTO mytable(id,type,last_name,first_name) VALUES (1,'A','Billy','John');
1 rows affected
INSERT INTO mytable(id,type,last_name,first_name) VALUES (2,'B','Bob','Joe');
1 rows affected
INSERT INTO mytable(id,type,last_name,first_name) VALUES (3,'A','Joe','Zeb');
1 rows affected
INSERT INTO mytable(id,type,last_name,first_name) VALUES (4,'C','Billy','John');
1 rows affected
SELECT x.id, x.type, x.last_name, x.first_name
FROM (
SELECT t.*, COUNT(DISTINCT type) OVER (PARTITION BY last_name, first_name) cnt
FROM mytable t
) x WHERE x.cnt > 1
| ID | TYPE | LAST_NAME | FIRST_NAME |
|---|---|---|---|
| 1 | A | Billy | John |
| 4 | C | Billy | John |