Conditional selection based on column value

Question

I have a table that can contain three different types of files. If file type A is present, select A; otherwise, if file type B is present and there is no Cs type with the same client_id, select B, otherwise select type C.
Some other magic will happen later, which will remove 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 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 hope to create a big nasty request to capture the following file based on the above criteria, which should lead to the following order if the request 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 made me the most remote 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 following selection, but all in one SQL statement.

Answer 1

You can use some nested analytics, although this looks a little more complicated than possible:

 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 this comes to the end result.

Or maybe a little better, and this time pull out only one record, which, I think, is the final goal inside the 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; 

The SQL script has been updated , showing the work again, so you can see that the evaluated order is what you requested.

In any case, it only gets into the table once, which may be an advantage, but you need to scan everything that may not ...

Answer 2

All this logic can be crowded in order with a statement. This works in your instance of the SQL script (thanks for the fact that this answer would not be combined without it). You pretty much ask for a choice * with an interesting expression in order. To fulfill this order (your second condition, b, where c does not exist), we will also need self-connectivity.

 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