You really need to actually read the article that has been suggested. If you had read it you would see that just sticking some values in a post is nothing like consumable data. We want and need to see ddl (create table) and data (insert statements). What you should have posted would look like this.

create table #Something

(

OrderNum varchar(20),

LineNum int,

Status char(1)

)

insert #Something

select '905055', 1, 'C' union all

select '905055', 2, 'C' union all

select '905055', 3, 'C' union all

select '905055', 4, 'O' union all

select 'AW00000253', 1, 'C' union all

select 'AW00000253', 2, 'C';

Then we would be able to see and work with the same thing. This makes it really easy for us to help you.

Somebody like myself can then come along and post a query that meets your requirements. Something like this one...

select *

from #Something

where OrderNum in

(

select OrderNum

from #Something

group by OrderNum

having MAX(Status) = 'C'

)