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