SQL SERVER - Find Second Highest Salary Of Employee

How to get second highest salary from an Employee table, the following solution is to get 2nd highest salary from Employee table:  

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

;WITH CTE AS

(

   SELECT DENSE_RANK() OVER( 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 Of Employee

In this SQL query I have used the DENSE_RANK() function,  this function will assign rank continuously and it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.

When DENSE_RANK() function executes, it will start assigning ranks from the first record starting from the integer value 1 to each record, until it got more than one record satisfying same condition for the rank i.e. if more than one record ties for a rank value, each tied records will be assigned same rank. In DENSE_RANK () the rank preserve its values even if there are more than one record ties for the same rank, but in  RANK () function this is not the case it does not always return consecutive integers.

By using DENSE_RANK(), we can get list of all employees getting Nth salary from the employee table.