I want to get employee name who is drawing the highest sal in each dept

• sivaj2k

  Ten Centuries

  Points: 1188

  More actions

  September 9, 2010 at 7:36 am

  #1218591

  Hi

  You have to use analytical functions to acheive department wise top salaries. We can achieve top 1..n salaries from the following query. We have to use Dense_Rank() function . Adding script to find Top salaries for each department.

  /* sample code to find top1 salaries

  author : siva kumar j

  date : 9-9-2010, 19.00 IST

  */

  create table #emp (empno int,ename varchar(10),sal decimal(10,2),deptno int)

  insert into #emp

  select 1001 empno,'raju' ename,20000.00 sal ,10 dept

  union

  select 1002,'rajesh',2000.00,20

  union

  select 1003,'suresh',3000.00,30

  union

  select 1004,'sravan',5000.00,20

  union

  select 1005,'rakesh',25000.00,10

  union

  select 1006,'anil',45000.00,20

  union

  select 1007,'babu',2600.00,30

  union

  select 1008,'scott',6000.00,20

  union

  select 1009,'prashant',8900.00,10

  union

  select 1010,'chandu',1000.00,20

  union

  select 1011,'kiran',40000.00,30

  union

  select 1015,'ranjith',25000.00,10

  union

  select 1012,'sat',25000.00,20

  select * from (

  select empno,ename,sal,deptno,DENSE_RANK() over(partition by deptno order by sal desc ) top_rank from #emp )a

  where top_rank = 1

  -- here by changing value for top_rank=2, we will get 2nd highest salaries for each department.

  drop table #emp