By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PersonsInitial (
tel int,
firstname varchar(255),
lastname varchar(255),
Customer_ID varchar(255)
);
insert into PersonsInitial(tel,firstname,lastname,Customer_ID) values
('01234','Manu','Johns','456'),
('01234','Manu','Johns','C456'),
('01234','Manu','Johns','C456'),
('21234','Fernand','Wajk','C389'),
('13554','Mathieu','Wainers','4683');
5 rows affected
with cte as (select row_number() over (partition by tel, firstname, lastname order by case when left(customer_id, 1) = 'C' then 0 else 1 end) rnk, p.*
from PersonsInitial p)
select *
from cte where rnk = 1
| rnk | tel | firstname | lastname | Customer_ID |
|---|---|---|---|---|
| 1 | 1234 | Manu | Johns | C456 |
| 1 | 13554 | Mathieu | Wainers | 4683 |
| 1 | 21234 | Fernand | Wajk | C389 |