Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Advanced
Dec 14, 2001

Department Wise Employee Ranking With Single T-SQL Query


This code will retrieve the employee details with a salary-wise ranking from each dept.EMP table with different departments and different salaries. If two employees in the same department have the same salary, then they'll have the same rank.
 
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
Srinivas Reddy
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap