MySQL 5.7

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

CREATE TABLE consignment ( id int, hawb int, col1 int, col2 int, service varchar(25) );
insert into consignment values (1,1,1,1,'CLRC');
insert into consignment values (2,1,1,1,'CLRC');
insert into consignment values (3,1,1,1,'CLRC');
insert into consignment values (4,1,1,2,'CLRC');
insert into consignment values (5,1,1,2,'CLRC');
select count(*) from
(
select distinct hawb, col1, col2, service 
  from consignment
) q 


count(*)


2


delete t1 
  from consignment t1
  join consignment t2 
 where t1.id < t2.id 
   and t1.hawb = t2.hawb
   and t1.col1=t2.col1
   and t1.col2=t2.col2   
   and t1.service='CLRC'   
select * from consignment


id
hawb
col1
col2
service


3
1
1
1
CLRC

5
1
1
2
CLRC