Write Query to get top N number of Employee of each department whose salary is maximum.

     One of my friend was facing issue in his development so I worked and solved his issue.I will try to share his requirement with respect to Employee table.
     His requirement was to show top 3 employee of each department having maximum salary.It seems to be bit confusing but it  is  not.
 Code :
We have following table :

Create Table Employee
(
EmployeeID Int Identity Primary Key,
EmployeeName Varchar(10),
DepartmentID Int ,
Salary Money
)

--Insert data

 INSERT INTO Employee
            (EmployeeName,
             DepartmentID,
             Salary)
VALUES      ('S12',
             1,
             2000),
            ('S13',
             1,
             3000),
            ('S14',
             1,
             4000),
            ('S15',
             1,
             5000),
            ('S16',
             1,
             6000),
            ('S17',
             1,
             7000),
            ('S18',
             1,
             8000),
            ('S19',
             1,
             9000),
            ('S110',
             1,
             10000),
            ('S11',
             2,
             1000),
            ('S12',
             2,
             2000),
            ('S13',
             2,
             3000),
            ('S14',
             2,
             4000),
            ('S15',
             2,
             5000),
            ('S16',
             2,
             6000),
            ('S17',
             2,
             7000),
            ('S18',
             2,
             8000),
            ('S19',
             2,
             9000),
            ('S110',
             2,
             10000),
            ('S11',
             3,
             1000),
            ('S12',
             3,
             2000),
            ('S13',
             3,
             3000),
            ('S14',
             3,
             4000),
            ('S15',
             3,
             5000),
            ('S16',
             4,
             6000),
            ('S17',
             4,
             7000),
            ('S18',
             4,
             8000),
            ('S19',
             4,
             9000),
            ('S110',
             4,
             10000)

Now we have data  as follow

Now question is ,really can we get it through query ?

Answer is yes.

Code :

;WITH cte
     AS (SELECT *,
                (SELECT Count(*)
                 FROM   employee
                 WHERE   salary>=e.salary
                        AND departmentID = e.departmentID) AS count
         FROM   Employee e)

SELECT *
FROM   cte
WHERE count <= 3

Logic is very simple but for understanding it will take some time.I will try to explain step by step.
1.We have main  table Employe. We will add one column in CTE as count.
2.Count will be calculated based on corelated subquery..
3.It will check how many employee of that department are getting equal are more than that  salary .
4.How value for  first emplyeeId is calculated with respect to DepartmentId
 -->
--First Row
  select *,(select  count(*) from Emplyee b where b.salary <=1000  and b.departmentID=a.departmentID
)

so for first row count will come as 10
EmployeeID EmployeeName DepartmentID Salary count
 1                 S11                           1                1000.00 10

--Second Row
  select *,(select  count(*) from Emplyee b where b.salary <=2000
and b.departmentID=a.departmentID
)

so for second row count will come as 9
EmployeeID EmployeeName DepartmentID Salary count
2                  S12                   1                 2000.00 9

 Like this all count  will be calculated with respect to departmentId
If we want to find out top 5 salary of employee we  only need to replace 3 by 5 in above query as follow
SELECT *
FROM   cte
WHERE count <= 5

  Same thing we can achieve through ranking function 

;
with cte as(
select * , rank() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee  emp
)
select * From cte where cn<=3 order by DepartmentID  asc



;
with cte as(
select * , row_number() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee emp
)
select * From cte where cn<=3 order by DepartmentID  asc




;
with cte as(
select * , dense_rank() over ( PARTITION by DepartmentID   order  by salary desc  )  cn
from Employee emp
)
select * From cte where cn<=3 order by DepartmentID  asc