CODEWITHSUNDEEP
sqlsql-server
youfree
youfree

Reputation: 39

Using Case in a select statement

Consider the following table

create table temp (id int, attribute varchar(25), value varchar(25))

And values into the table

insert into temp select 100, 'First', 234
insert into temp select 100, 'Second', 512
insert into temp select 100, 'Third', 320
insert into temp select 101, 'Second', 512
insert into temp select 101, 'Third', 320

I have to deduce a column EndResult which is dependent on 'attribute' column. For each id, I have to parse through attribute values in the order First, Second, Third and choose the very 1st value which is available i.e. for id = 100, EndResult should be 234 for the 1st three records.

Expected result:

|  id | EndResult |
|-----|-----------|
| 100 |       234 |
| 100 |       234 |
| 100 |       234 |
| 101 |       512 |
| 101 |       512 |

I tried with the following query in vain:

select id, case when isnull(attribute,'') = 'First'
                then value
            when isnull(attribute,'') = 'Second'
                then value         
            when isnull(attribute,'') = 'Third'
                then value    
            else '' end as EndResult
from
temp

Result

|  id | EndResult |
|-----|-----------|
| 100 |       234 |
| 100 |       512 |
| 100 |       320 |
| 101 |       512 |
| 101 |       320 |

Please suggest if there's a way to get the expected result.

Upvotes: 1

Views: 110

Answers (4)

Ray Krungkaew
Ray Krungkaew

Reputation: 6965

keep it simple 

;with cte as
(
    select  row_number() over (partition by id order by (select 1)) row_num, id, value
    from    temp
)
select  t1.id, t2.value
from    temp t1
            left join cte t2
            on t1.Id = t2.id
where   t2.row_num = 1

Result 

id   value
100  234
100  234
100  234
101  512
101  512

Upvotes: 0

FutbolFan
FutbolFan

Reputation: 13713

Here is how you can achieve this using ROW_NUMBER():

WITH t
AS (
    SELECT *
        ,ROW_NUMBER() OVER (
            PARTITION BY id ORDER BY (CASE attribute WHEN 'First' THEN 1 
                                                     WHEN 'Second' THEN 2 
                                                     WHEN 'Third' THEN 3 
                                      ELSE 0 END)
            ) rownum
    FROM TEMP
    )
SELECT id
    ,(
        SELECT value
        FROM t t1
        WHERE t1.id = t.id
            AND rownum = 1
        ) end_result
FROM t;

For testing purpose, please see SQL Fiddle demo here:

SQL Fiddle Example

Upvotes: 1

Deepshikha
Deepshikha

Reputation: 10264

In case the attribute column have different values which are not in alphabetical order as is the case above you can write as:

with cte as
(
  select id,
         attribute,
         value,
         case attribute when 'First' then 1
                        when 'Second' then 2 
                        when 'Third' then 3 end as seq_no 
         from temp
  )
  , cte2 as 
  ( 
    select id,
           attribute,
           value,
           row_number() over ( partition by id order by seq_no asc) as rownum
     from cte
    ) 
    select T.id,C.value as EndResult
    from temp T
    join cte2 C on T.id = C.id and C.rownum = 1

DEMO

Upvotes: 2

GolezTrol
GolezTrol

Reputation: 116100

You can use analytical function like dense_rank to generate a numbering, and then select those rows that have the number '1':

select
  x.id,
  x.attribute,
  x.value
from
  (select
    t.id,
    t.attribute,
    t.value,
    dense_rank() over (partition by t.id order by t.attribute) as priority
  from
    Temp t) x
where
  x.priority = 1

In your case, you can conveniently order by t.attribute, since their alphabetical order happens to be the right order. In other situations you could convert the attribute to a number using a case, like:

order by 
  case t.attribute 
    when 'One' then 1 
    when 'Two' then 2 
    when 'Three' then 3 
  end

Upvotes: 3

Related Questions