Writing SQL query: slightly complicated -

i have table primary key id , 2 columns order , status. value status of 'a', 'p', 's', 'o' or 'c'. need to find values of order status 'a' or 'c'. how should write sql query that? know basic sql unable results using that.

sample table:

id  order   status  1   1234     2   2343    p  3   4351    s  4   8675    c  5   9867     6   9867    c  7   1234     8   2343     expected result:  1234  8675  9867 

select distinct t.[order] table1 t     not exists     (          select *          table1 t2 t2.[order] = t.[order] , t2.status not in ('a', 'c')     ) 

or

select t.[order] table1 t group t.[order] having     sum(case when [status] in ('a', 'c') 1 else 0 end) = count(*) 

or

select distinct t.[order] table1 t     t.[order] not in (select t2.order table1 t2 t2.status not in ('a', 'c')) 

see sql fiddle example details

btw, order not best name column, avoid using keywords column names