Department Wise Employee Ranking With Single T-SQL Query

 
EMP Table
----------


DEPT        NAME                      SALARY$
----------- ------------------------- ----------------
10          JAMES                     2000.00000
10          ADAMS                     1500.00000
10          GEORGE                    2000.00000
10          JAY                       3000.00000
20          LARANCE                   3000.00000
20          NICOLES                   4000.00000
20          DAVID                     2000.00000
20          BORDER                    1500.00000
10          BOB                       3000.00000
10          SMITH                     1000.00000


select b1.dept,b1.name,b1.salary$,rank= _
(select count(distinct salary$)+1
from emp b2 where
b1.dept=b2.dept and b2.salary$>b1.salary$)
from emp b1 order by dept,b1.salary$ desc


Result
-------


dept      name                   salary$              rank
--------- ---------------------- -------------------- -----
10        JAY                    3000.00000           1
10        BOB                    3000.00000           1
10        JAMES                  2000.00000           2
10        GEORGE                 2000.00000           2
10        ADAMS                  1500.00000           3
10        SMITH                  1000.00000           4
20        NICOLES                4000.00000           1
20        LARANCE                3000.00000           2
20        DAVID                  2000.00000           3
20        BORDER                 1500.00000           4