Conditional select based on column value

You could use some nested analytics, though this looks a bit more complicated than it probably should:

select id, type, client_idfrom (  select t.*,    case when type = 'a'then 1      when type = 'b' and c_count = 0 then 2      when type = 'c' then 3    end as rnk  from (    select f.*,      sum(case when type = 'a' then 1 else 0 end)        over (partition by client_id) as a_count,      sum(case when type = 'b' then 1 else 0 end)        over (partition by client_id) as b_count,      sum(case when type = 'c' then 1 else 0 end)        over (partition by client_id) as c_count    from files f  ) t)order by rnk;

SQL Fiddle showing how that builds up to the final result.

Or maybe a bit better, and this time only pulling a single record which I think is the end goal inside a loop (?):

select id, type, client_idfrom (  select t.*,    dense_rank() over (      order by case when type = 'a' then 1        when type = 'b' and c_count = 0 then 2        when type = 'c' then 3      end, client_id) as rnk  from (    select f.*,      sum(case when type = 'c' then 1 else 0 end)        over (partition by client_id) as c_count    from files f  ) t)where rnk = 1;

Updated SQL Fiddle, showing working again, so you can see the evaluated order is what you asked for.

Either way this only hits the table once, which may be an advantage, but has to scan the whole thing, which may not...

All this logic can be crammed into the order by statement really. This works in your SQL fiddle instance (thanks for providing that, this answer wouldn't have come together without it). You are pretty much asking for a select * with an interesting order by statement. To do this order by (your second condition, b where c doesn't exist) we will need a self join too.

select f.*from files fleft join files a on a.client_id = f.client_id and f.type = 'b' and a.type = 'c'order by case when f.type = 'a' then 1  when f.type = 'b' and a.id is null then 2  when f.type = 'c' then 3  when f.type = 'b' and a.id is not null then 4else 5 end