SubQueryFind 2^ndHighest Salary in SQL SERVER

There are multiple ways to find the second-highest salary in SQL

Creating Table:- The first step is to create a table

CREATE TABLE Employee 
( 
ID INT IDENTITY(1,1) PRIMARY KEY, 
NAME VARCHAR(40), 
SALARY MONEY 
)

Then Inserting Values in Table

INSERT INTO Employee VALUES('Mandy',12000)
INSERT INTO Employee VALUES('Chris',15000)
INSERT INTO Employee VALUES('Henry',10000)
INSERT INTO Employee VALUES('Katy',10000)
INSERT INTO Employee values('Adams',11000)

The above query will give you as shown below

So, we can find the N^th highest salary using the following methods

1, Using TOP Keyword

SELECT TOP 1 Salary
       FROM (SELECT DISTINCT TOP N Salary 
	                         FROM Employee 
	                         ORDER BY Salary DESC) AS Employee_Temp 
       ORDER BY Salary

Then substitute the letter 'N" with 2 to find 2^ndhighest Salary

SELECT TOP 1 Salary
       FROM (SELECT DISTINCT TOP 2 Salary 
	                         FROM Employee 
	                         ORDER BY Salary DESC) AS Employee_Temp 
       ORDER BY Salary

2, Using COmmon Table(CTE)

WITH Highest_Salary
AS
(
SELECT Salary
       ,DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank 
	    FROM Employee
)
SELECT Salary 
       FROM Highest_Salary AS CTE
	   WHERE CTE.SalaryRank = 2

3, Using SubQuery

SELECT MAX(salary) AS salary
      FROM employee
      WHERE salary < (SELECT MAX(salary) 
                 FROM employee
)
----------------------------3rd Highest----------------------------------------------------------
SELECT MAX(salary) AS salary
      FROM employee
      WHERE salary < (SELECT MAX(salary) 
                 FROM employee
                 WHERE salary < (SELECT MAX(salary)
                 FROM employee)
                )

FIND THE 2ND HIGHEST SALARY in EACH DEPARTMENT

---Using CTE
WITH Highest_Salary
AS
(
SELECT Salary
       ,DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank 
	    FROM Employee 
)
SELECT Salary 
       FROM Highest_Salary AS CTE
	   WHERE CTE.SalaryRank = 2
------------------FIND THE 2ND HIGHEST SALARY in EACH DEPARTMENT-----------------------------------
WITH cteRowNum AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY deptId ORDER BY Salary DESC) AS RowNum
    FROM tbl_emp
 )
 SELECT dept.deptid,cte.salary as highestSalary
 FROM cteRowNum cte 
JOIN tbl_department dept 
ON cte.deptid=dept.deptid
 WHERE RowNum = 2;