SQL Query - How to Select top 2 highest salaried employees from each department?

Download sample data with Schema

Let us share some knowledge of SQL Query.

I have three tables mentioned below:

• Employee – Master table
• SalaryDetails – Reference table with salary information
• Department – Master table

The sample data into these tables is show below:

Employee	Department
Salary Details

Now the SQL Query to be prepared should contain resultant data as ‘top 2 highest salaried employees from each department’ as below :

Resultant Data

Please provide your queries in comment of this article.

Schema with sample data is provided on download link, which will save your time for preparing sample tables and data. Click here to download.

Following are the solutions provided by:
Me (Chirag Vidani)

select 
 data.DepartmentName 'Department Name', data.EmployeeName 'Employee Name', data.Salary 'Salary/Day' 
from
 (select 
  d.Name 'DepartmentName',e.Name 'EmployeeName',sd.Salary 'Salary',dense_rank() over(partition by sd.DepartmentId order by sd.Salary desc)'SalaryRank' 
 from 
  Salarydetails sd
 join Department d 
  on sd.DepartmentId = d.Id
 join Employee e 
   on sd.EmployeeID =e.Id) as data
where 
 data.SalaryRank <= 2
order by 
 data.DepartmentName,data.Salary desc

Mr. Kaushik Patel

SELECT 
 d.* 
FROM
 (select 
  S.*,D.NAME DepartmentName,E.NAME EmployeeName ,RANK() OVER (PARTITION BY DeptID ORDER BY SALARY DESC) AS Raw_Rank 
 from 
  Salary s 
 inner join Employee e 
  on s.EmployeeId = e.id 
 inner join Department d 
  on s.DepartmentId = d.id ) d 
WHERE 
 Raw_Rank <=2