title: Conditional select based on column value
body: <p>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.<br>
  Some other magic will happen later which removes the selected file from the table.  </p>
  
  <p>I have the following table in an Oracle 10g SQL database:</p>
  
  <pre><code> ID   | TYPE | CLIENT_ID
  ########################
  file1 | A    | 1
  file2 | B    | 1
  file3 | C    | 1
  file4 | B    | 2
  </code></pre>
  
  <p>and for those who want to follow along at home, <a href="http://sqlfiddle.com/#!4/a2d2a9" rel="nofollow noreferrer">sqlfidde</a> or sql:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>#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)
  </code></pre>
  
  <p>The attempt that got me the farthest was to write three separate queries for each type:</p>
  
  <pre><code>--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'
  </code></pre>
  
  <p>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.  </p>