Aetherix Aetherix - 1 month ago 8
SQL Question

Picking one row over another

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

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.