CODEWITHSUNDEEP
sql-server-2008
HaXoR
HaXoR

Reputation: 23

how to use rank with case in SQL server

Query 

select 
    territory, new,
    rank() over (order by new ASC) as rank_1  
case 
when rank_1 <= (select count(new)/3 from ##final) then 'low'
when rank_1 <= (select (count(new)/3)*2 from ##final) then 'medium'
when rank_1 <= (select count(new) from ##final) then 'high'
else 'error'
end 
from 
    ##final 
order by 
    territory

table

territory       new
6310101     941
6310102     186
6310103         458
6310103     458
6310104     109
6310105     113
6310106     305
6310107     189
6310108     184
6310109     737
6310110     862
6310110     862
6310201     1079
6310202     236
6310203     1529

Error message:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'case'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'then'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'then'.

Upvotes: 0

Views: 3697

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460068

You cannot refer to this column directly, you need a sub-query or CTE:

WITH CTE AS
(
   SELECT territory, new,
        RANK() Over (Order By new ASC) as rank_1  
   FROM ##final 
)
SELECT territory, new, 'error' =
       case 
           when rank_1 <= (select count(new)/3 from ##final) then 'low'
           when rank_1 <= (select count(new)/3 from ##final) then 'medium'
           when rank_1 <= (select count(new)/3 from ##final) then 'high'
       else 'error' end 
FROM CTE
ORDER BY territory

Demo

Upvotes: 2

Related Questions