CODEWITHSUNDEEP
sqlsql-servert-sql
Aetherix
Aetherix

Reputation: 2250

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?

Upvotes: 7

Views: 2098

Answers (6)

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

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.

Upvotes: 5

Charles Bretana
Charles Bretana

Reputation: 146469

Then try this

 Select distinct a.id, 
    coalesce(t1.type, t9.type) type,
    case when t1.type is not null 
         then t1.value else t9.value end value
 From table a 
    left join table t1 
       on t1.id = a.id 
          and t1.type = 'Z01'
    left join table t9 
       on t9.id = a.id 
          and t9.type = 'Z09'
  Where a.type in ('Z01', 'Z09')  -- < -- this last to eliminate row E

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Start with a WHERE clause. Select only entries with Z01 and Z09. Then use ROW_NUMBER to rank your rows and keep the best.

select id, type, value 
from
(
    select 
      id, type, value, 
      row_number() 
        over (partition by id order by case when type = 'Z01' then 1 else 2 end) as rn
    from mytable
    where type in ('Z01','Z09')
) ranked
where rn = 1;

Upvotes: 0

Brian Rudolph
Brian Rudolph

Reputation: 6308

Something like this:

select distinct
base.Id,
Coalesce(z01.Type, any.Type) Type,
Coalesce(z01.Value, any.Value)
from (select distinct id from [table]) base
left outer join [table] z01
    on z01.id = base.id
    and z01.Type = 'Z01'
left outer join [table] any
    on any.id = base.id
    and any.type != 'Z01'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269643

I would use window functions:

select t.*
from (select t.*,
             row_number() over (partition by id
                                order by (case when type = 'Z01' then 1
                                               when type = 'Z09' then 2
                                          end)
                               ) as seqnum
      from t
     ) t
where seqnum = 1;

I don't fully understand teh statement "if nothing is available, then choose nothing". If, by this, you mean you want 'Z01', 'Z09' or nothing at all, then just do:

select t.*
from (select t.*,
             row_number() over (partition by id order by type) as seqnum
      from t
      where type in ('Z01', 'Z09')
     ) t
where seqnum = 1;

The where clause does the filtering, and you can just order by the type alphabetically.

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use query like this

;WITH cte
AS (SELECT
    *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY type) AS rn
FROM yourtable
WHERE type IN ('Z01', 'Z09'))
SELECT
    id, type, value
FROM cte
WHERE rn = 1

Upvotes: 0

Related Questions