Picking one row over another

Question

I have a table that looks like this:

ID   Type   Value
A    Z01    10
A    Z09    20
B    Z01    30
C    Z01    40
D    Z09    50
E    Z10    60

For each ID I would like to retrieve a value. Ideally the value should come from the row with type Z01. However, if Z01 is not available I'll pick Z09 instead. If nothing is available I would like to select nothing.

The result would look like this:

Id   Type   Value
A    Z01    10
B    Z01    30
C    Z01    40
D    Z09    50

How can I accomplish this with T-SQL?

Answer 1

This should give you what you want:

select *
from table t
where 1 = case 
              when t.type = 'Z01' 
                  then 1 
              when t.type = 'Z09' 
                    and not exists (select 1 from table where id = t.id and type = 'Z01')
                  then 1 
          else 0 
       end

An alternative, with using a more common approach is (re-writing the CASE expression):

select *
from table
where type = 'Z01'
    OR (type = 'Z09' and not exists (select 1 from table where id = t.id and type = 'Z01'))

An obvious sargable approach (which will make your query use the appropriate index on your table, if it exists) would be:

select *
from table
where type = `Z01`

union all
select *

from table
where type = `Z09`
    and not exists (select 1 from table where id = t.id and type = 'Z01')

And when I'm saying index I'm talking about a non-clustered index on the type column.