Conditional select based on column value

Question

I have a table that may contain three different file types. If file type A is present, select A, else if file type B is present AND there are no type Cs with the same client_id, select B, else select a type C.
Some other magic will happen later which removes the selected file from the table.

I have the following table in an Oracle 10g SQL database:

 ID   | TYPE | CLIENT_ID
########################
file1 | A    | 1
file2 | B    | 1
file3 | C    | 1
file4 | B    | 2

and for those who want to follow along at home, sqlfidde or sql:

create table files (
  id varchar(8)  primary key,
  type varchar(4),
  client_id number
);
insert into files values ('file1', 'A', 1);
insert into files values ('file2', 'B', 1);
insert into files values ('file3', 'C', 1);
insert into files values ('file4', 'B', 2);

I am hoping to create a big nasty query to grab the next file based on the above criteria, which should result in the following order if the query was run four times:

#1: file1, A, 1 (grab any As first)
#2: file4, B, 2 (grab any Bs who don't have any Cs with the same client_id)
#3: file3, C, 1 (grab any Cs)
#4: file2, B, 1 (same as run #2)

The attempt that got me the farthest was to write three separate queries for each type:

--file type 'A' selector
select * from files where type = 'A'
--file type 'B' selector
select * from files where type = 'B' and client_id = (
  select client_id from files group by client_id having count(*) = 1
);
--file type 'C' selector
select * from files where type = 'C'

I want to check the number of rows returned after each and if it is 0 use the next select, but all in one SQL statement.

Answer 1

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

select id, type, client_id
from (
  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_id
from (
  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...

Answer 2

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 f
left 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 4
else 5 end