Get records which doesn't have duplicate records

Question

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.

I need to avoid 23442242 from the result set.

Answer 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;

Answer 2

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);

Answer 3

select distinct PolicyNO from atable
where PolicyNO not in(
select PolicyNO
from atable
group by PolicyNO, ClassCode
having count(*)>1
);