SQL Query to find the Second largest salary (or value) of the employee by department name (or id)
This example also useful for find the second largest value from every group.
Step 1: Create Employee Table that contain Id, Name, Email, Contact, Salary and Department columns like below:
Step 2: Enter Details in the employee table
Step 3: Now execute our query.
1) Get All the data by department and salary.
SELECT * FROM [TestDB].[dbo].[Employee] order by Department,salary desc
2) Get Second largest salary from the Employee Table
SELECT MAX(salary) FROM [TestDB].[dbo].[Employee] where salary<(select max(salary) from [TestDB].[dbo].[Employee])
3) Final Query – Get second largest salary from the Employee Table by Department Name
select Salary,Department from ( select ROW_NUMBER() over (partition by Department order by salary desc) as 'rowNum', salary,Department from [TestDB].[dbo].[Employee]) withRowNum where rowNum = 2
Hope this helps you.