We help IT Professionals succeed at work.

SQL 2005 - rank functions

niceguy971 asked
There is a table which shows bonus received by each employee within department. What would be the best way to get the second employee who received maximum bonus within department??

For department Receiving: it's John White... 700

For department Orders; it's Chris Abcd...200

The *.Xls (showing the content of the table ) is attached.
Watch Question

Senior Developer Analyst
ROW_NUMBER() using Bonus descending would do a good job at this.
declare @myData table (name nvarchar(50), bonus money, department  nvarchar(25))

insert into @myData values('Mike Smith',600,	'Receiving')
insert into @myData values('John White',	700,	'Receiving')
insert into @myData values('Brian Green',	800,	'Receiving')
insert into @myData values('Chris Abcd',	200,	'Orders')
insert into @myData values('Tania xyz',	400,	'Orders');

with MyData_Instances as
	select	*, ROW_NUMBER() over (Partition By Department Order By Bonus desc) as Instance_Counter

	from	@myData
select	*
from	MyData_Instances
where	Instance_Counter = 2

Open in new window

Top Expert 2011
select *
   from (
select x.*
         ,row_number() over (partition by department order by name ) as rn
 from (select x.*
                   ,max(bonus) over (partition by department ) as maxbonus
              from yourtable
                as x
         ) as x            
where maxbonus=bonus
) as y
where y.rn=2

--- use whatever criteria you have for determining the order of those receiving the bonus
     in the order by clause   ... as rn

SELECT Department ,Name ,Bonus
FROM      (      SELECT   Department ,Name ,Bonus
                              ,RANK() OVER (PARTITION BY Department ORDER BY Bonus DESC) AS Rank
                  FROM dbo.YourTable(NOLOCK)
            ) AS A
WHERE A.Rank = 2