SQL Query to find the Second largest salary (or value) of the employee by department name (or id)

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