How To Get Second Highest Salary Using Query In SQL

In this article, we will learn how to get the second highest salary in SQL.

Below is a selection from the “tblEmployees” table:

SELECT * FROM tblEmployees ORDER BY Salary DESC;

  • Using Sub-Query 1:
SELECT MAX(Salary) AS ScndHighestSalary 
FROM tblEmployees 
WHERE SALARY < (SELECT MAX(Salary) FROM tblEmployees);
  • Using Sub-Query 2:
SELECT TOP 1 Salary AS ScndHighestSalary
FROM (
    SELECT DISTINCT TOP 2 Salary 
      FROM tblEmployees 
    ORDER BY Salary DESC 
   ) RESULT 
ORDER BY Salary;
  • Using CTE (Common Table Expressions):
WITH RESULT AS
(
  SELECT Salary,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DENSERANK
  FROM tblEmployees
)
SELECT TOP 1 Salary AS ScndHighestSalary
FROM RESULT
WHERE DENSERANK = 2;

Output

All 3 methods return the same output.

 

Also, check How To Use String Functions In SQL

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe

Select Categories