top 1 case in select statement on TSQL

Question

I am having problem on this query. How can I fix this:

select (select case top 1 STATUS 
       when 'Inprocess' then 'Processing' 
       when 'Inworkbin' then 'Waiting In Draft' 
       end 
    from ICS_EMAIL_CONNECTIONS_TRX A    
    where A.SESSIONID = B.SESSIONID 
    and STATUS <> 'Completed'
    order by A.CREATE_DATE desc) as LAST_STATUS

I am getting this error:

Incorrect syntax near the keyword 'top'.

Any suggestions?

score 12 · Accepted Answer · answered May 02 '13 at 11:53

12

Try:

select top 1 case STATUS

instead of

select case top 1 STATUS

answered May 02 '13 at 11:53

TechDo

18,398
3
51
64

all answers are true but I should select one of them as correct answer :) thanks everyone – cihadakt May 02 '13 at 12:02

score 4 · Answer 2 · answered May 02 '13 at 11:53

You don't need the nested select.

select top 1 case STATUS 
             when 'Inprocess' then 'Processing' 
             when 'Inworkbin' then 'Waiting In Draft' 
             end LAST_STATUS
from ICS_EMAIL_CONNECTIONS_TRX A    
where A.SESSIONID = B.SESSIONID 
and STATUS <> 'Completed'
order by A.CREATE_DATE desc;

Although this can return 0 rows whereas your original form with the TOP 1 written properly will always return one row, even if the value is NULL.

score 2 · Answer 3 · answered May 02 '13 at 11:53

select (select top 1 (case STATUS 
   when 'Inprocess' then 'Processing' 
   when 'Inworkbin' then 'Waiting In Draft' 
   end) 
from ICS_EMAIL_CONNECTIONS_TRX A    
where A.SESSIONID = B.SESSIONID 
and STATUS <> 'Completed'
order by A.CREATE_DATE desc) as LAST_STATUS

score 2 · Answer 4 · answered May 02 '13 at 11:54

corrrect the case and top keyword

select (select top 1 case STATUS 
       when 'Inprocess' then 'Processing' 
       when 'Inworkbin' then 'Waiting In Draft' 
       end 
    from ICS_EMAIL_CONNECTIONS_TRX A    
    where A.SESSIONID = B.SESSIONID 
    and STATUS <> 'Completed'
    order by A.CREATE_DATE desc) as LAST_STATUS

score 1 · Answer 5 · answered May 02 '13 at 12:18

select top 1 * from (select case STATUS 
       when 'Inprocess' then 'Processing' 
       when 'Inworkbin' then 'Waiting In Draft' 
       end 
    from ICS_EMAIL_CONNECTIONS_TRX A    
    where A.SESSIONID = B.SESSIONID 
    and STATUS <> 'Completed'
    order by A.CREATE_DATE desc) as LAST_STATUS

top 1 case in select statement on TSQL

5 Answers5