Find Second Highest Salary

We can use any of the below keywords to find the second highest value from a table.

  • MAX, which returns the maximum value of a column
  • LIMIT, which returns a specific number of rows from a table
  • TOP, which returns a specific number of top rows from a table

Let's assume a table with the below table definition and data, where we need to find the second highest salary.

ID     Name     Salary
===========================
1    Arun       20000
2    Kiran      15000
3    James      35000
4    Sammy      50000
5    Martin     25000

SQL Query Using MAX

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);

In the above query, we have an inner query within the outer query, so the inner query executes first and is followed by the outer query as explained below.

  • The inner query executes and returns the maximum salary from the table, which is 50000.
  • Then, the outer query executes with the below WHERE condition, which returns the second maximum salary from the table, which is 35000.
    • WHERE salary < 50000

SQL Query Using LIMIT

SELECT salary FROM (SELECT salary FROM employees ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary ASC LIMIT 1;

In the above query, we have an inner query within the outer query, so the inner query executes first and is followed by the outer query as explained below.

  • The inner query sorts the table rows by salary in descending order and returns the first 2 rows with salary amounts of 50000 and 35000 respectively.
  • Then, the outer query sorts the inner query results by salary in ascending order and returns the first 1 row with a salary amount of 35000, which is the second maximum salary.

SQL Query Using TOP

SELECT TOP 1 salary FROM FROM (SELECT TOP 2 salary FROM employees ORDER BY salary DESC) as emp ORDER BY salary ASC;

In the above query, we have an inner query within the outer query, so the inner query executes first and is followed by the outer query as explained below.

  • The inner query sorts the table rows by salary in descending order and returns the top 2 salary amounts 50000 and 35000 respectively.
  • Then, the outer query sorts the inner query results by salary in ascending order and returns the top 1 salary amount of 35000, which is the second maximum salary.

Overall

We now know the SQL Query to find the second maximum value from a specific table column.

Related Links