If salary of employee is up to 5000 then rank =1, up to 100k then rank = 2 and so on. can we get the result using rank function?

Question

If salary of employee is below 50,000 then rank = 1, up to 100 000 then rank 2, up to 150 000 then rank 3.Can I get this using rank function?

SELECT CASE WHEN SALARY BETWEEN 1 , 50000 then 1 
else case when salary between 50000 , 100000 then 2
else case when salary between 150000 , 200000 then 3 else 4 end as rank FROM EMPLOYEE WHERE EMP = 1

@stickybit, his use of `BETWEEN` seems legitimate here - the case of 50,000.50 will be ranked as 2, in proper accordance with the logic. — Steve, May 23 '19 at 11:31
you most probably want to use ntile function https://stackoverflow.com/questions/24924591/how-can-i-group-student-scores-into-quintile-using-sql-server-2008 . — Dr Phil, May 23 '19 at 12:17

score 1 · Answer 1 · answered May 23 '19 at 11:30

From your comments, it seems that you just need a way to calculate the rank for intervals of 50,000, so you don't have to use successive CASE expressions. This can be done easily enough using simple integer division, rather than the RANK function:

SELECT *, CAST(salary / 50000 AS INT) + 1 AS Rnk
FROM Employee

gives you these results:

+-----+-----------+-----+
| emp |  salary   | rnk |
+-----+-----------+-----+
|   1 |  49000.00 |   1 |
|   2 |  50000.00 |   2 |
|   3 |  99000.00 |   2 |
|   4 | 100000.00 |   3 |
|   5 | 149000.00 |   3 |
|   6 | 150000.00 |   4 |
+-----+-----------+-----+

If desired, you could also add this as a calculated column (and index it) as desired.

Stanislovas Kalašnikovas · Answer 2 · 2019-05-23T11:00:39.603

0

Your syntax is incorrect, there is how CASE should be used:

SELECT 
    CASE 
        WHEN salary BETWEEN 1 AND 49999 THEN 1 
        WHEN salary BETWEEN 50000 AND 99999 THEN 2
        WHEN salary BETWEEN 100000 AND 150000 THEN 3 
        ELSE 4 
    END AS rnk 
FROM EMPLOYEE 
WHERE EMP = 1 -- I'm not sure what you trying to filter here

edited May 23 '19 at 11:00

answered May 23 '19 at 10:55

Stanislovas Kalašnikovas

9,203
4
32
59

i wanted to know if i can replace the case function using rank function – manoj sai surya srikanta May 23 '19 at 11:00
@manojsaisuryasrikanta why do you need to replace it? `RANK` should be used for other purposes. – Stanislovas Kalašnikovas May 23 '19 at 11:02
if i have to calculate the rnk for salary up to 800000 then i have to write 16 case statements. so, i wanted to make it simple. please suggest if there is any other way around – manoj sai surya srikanta May 23 '19 at 11:15
For ranking you don't defined the cutoff. It is rather to divide the records in groups by say top quieter, two medium and bottom. Better you use a defined cutoff – Dr Phil May 23 '19 at 11:28

If salary of employee is up to 5000 then rank =1, up to 100k then rank = 2 and so on. can we get the result using rank function?

2 Answers2