0

I have a table with polcies and class codes, I want to get all policies which doesn't have duplicate class codes. Do you have any idea? One policy may have one or more class codes. If any class code is present for more than one time then I want avoid that policy in the result set.

enter image description here

I need to avoid 23442242 from the result set.

  • Your question isn't clear. Do you want to avoid all of the policies highlighted in bold, or all of the ones with the same policy number as well as those highlighted in bold? – Philᵀᴹ Dec 11 '13 at 23:59
  • I want to avoid all 23442242, becuse it has a duplicate class code as highlighted. – user1614862 Dec 12 '13 at 0:01
1

Assuming you just want to avoid the records that're bold:

Setup:

create table simplequestion 
(
 p number,
 c number  
);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (53453453,3454);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (23442242,4567);

insert into simplequestion values (23442242,4667);

insert into simplequestion values (53453453,5656);

insert into simplequestion values (53535354,3543);

insert into simplequestion values (34543545,4354);

Query:

select p,c,count(*)
from simplequestion
group by p,c
having count(*)=1;
  • not just the bold ones....I need to avoid all 23442242 because 23442242 has one duplicate class code. I highlighted to show that it has duplicate. – user1614862 Dec 12 '13 at 0:57
1

This one should do it... maybe someone else can optimize it:

select * from simplequestion where p not in (
 select p from simplequestion where c in(
   select c from simplequestion having count(c) > 1 group by c));

Based on

create table simplequestion 
(
 p number,
 c number  
);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (53453453,3454);

insert into simplequestion values (23442242,3455);

insert into simplequestion values (23442242,4567);

insert into simplequestion values (23442242,4667);

insert into simplequestion values (53453453,5656);

insert into simplequestion values (53535354,3543);

insert into simplequestion values (34543545,4354);

by Phil

UPDATE:
After inserting another row into your table:

insert into simplequestion values (53535354,3455);

Try the following

select * from simplequestion where p not in (
select p from simplequestion where c in(
   select c from simplequestion having count(c) > 1 group by c
     )
     having count(p) > 1 group by p);
  • it is not working, because if the class code 3455 is present for 53453453 policy then it is skipping that policy also from the result set. – user1614862 Dec 12 '13 at 19:49
0
select distinct PolicyNO from atable
where PolicyNO not in(
select PolicyNO
from atable
group by PolicyNO, ClassCode
having count(*)>1
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.