SQL SERVER - Second Highest Salary Of Employee From Each Department

Using DENSE_RANK()  function we can get second highest salary from an Employee table for each department. The following is the solution to get 2nd highest salary from Employee table for each department:  

DECLARE @Employees TABLE

(

EmployeeID INT IDENTITY(1,1),

EmployeeName VARCHAR(15),

Department VARCHAR(15),

Salary NUMERIC(16,2)

)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Barry','IT', 80000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Jones','IT', 40000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Billy','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Ralph','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Aaron','Finance', 40000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Barney','Finance', 25000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Callum','Finance', 25000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Oliver','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Anderson','HR', 25000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Martin','HR', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Garcia','HR', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)

VALUES('Caine','HR', 10000)

-- Find the second highest salary of employee from each department

;WITH CTE AS

(

   SELECT DENSE_RANK() OVER( PARTITION BY Department ORDER BY Salary DESC ) as [Rank],

   EmployeeName,

   Department,

   Salary

   FROM @Employees

)

SELECT

x.EmployeeName,

x.Department,

x.Salary

FROM CTE x

WHERE x.[Rank]  = 2

ORDER BY x.Salary

The output of the above query is: 

Second highest salary from each department