Seven Ways to Find Second Highest Salary in MySQL

Introduction

We can write SQL queries to get a particular column value using a single table or joining multiple tables. We can use the WHERE condition to filter data and various SQL functions to get the required data. Here, we will use MySQL to write SQL statements to get the second-highest salary from the “employees” table. You can use a generic statement also by saying "how to get nth-highest salary using SQL". We will use phpMyAdmin SQL window to write and execute the query. We are using employee's salary as an example; however, you can use these queries to extract similar data from other tables by changing the table name and column name.

Create a table in MySQL

Let us first create a table named "employees" in MySQL. We will insert some data in this table. Create table scripts with insert statements are given below:

The structure of the employees table and data are given below:

employees table structure

Table data:

employees table data

If we select the data from this table with descending order of salary (SELECT * FROM employees order by salary desc), we get below output, second highest salary is marked:

employees table structure

We will write the queries to get the second row above. Our query output should give emp_id, name, email, gender and salary.

Assumption: We have assumed here that there are no duplicate salaries, i.e., two or more employees do not have the same salary.

Write SQL queries to get the second-highest salary

We will use phpMyAdmin SQL query window to write and execute the queries. There are several ways to get the second-highest salary using MySQL. Let us write the queries to get the second-highest salary from the "employees" table. The first three queries do not use LIMIT clause in the SQL statement.

Query 1:

Find second-highest salary without using LIMIT in the SQL statement.

Output:

mysql query second highest salary without using limit

If you want to get just the salary value without employee details, you do not need the outermost select statement.

Query 2:

Find second-highest salary without using LIMIT in the SQL statement.

Output:

mysql query second highest salary without using limit

If you want to get just the salary value without employee details, you do not need the outermost select statement.

Query 3:

Find second-highest salary without using LIMIT in the SQL statement.

Output:

mysql query second highest salary without using limit

Query 4:

This query uses LIMIT clause in SQL statement.

Output:

mysql query second highest salary without using limit

Query 5:

This query uses LIMIT and OFFSET in SQL statement.

It uses LIMIT 1 and OFFSET 1. For nth highest salary use LIMIT n - 1, 1, this means LIMIT 1 OFFSET n - 1. So for 3rd highest use LIMIT 2, 1 or LIMIT 1 OFFSET 2.

Output:

mysql query second highest salary without using limit

Query 6:

This query uses LIMIT in SQL statement.

For nth highest salary use LIMIT n in the above query. So for 3rd highest use LIMIT 3.

Output:

mysql query second highest salary without using limit

Query 7:

This query also uses LIMIT in SQL statement.

For nth highest salary use LIMIT n in the inner query above. So for 3rd highest use ORDER BY salary DESC LIMIT 3.

Output:

mysql query second highest salary without using limit

Conclusion

I have given a few SQL statements here to get the second-highest salary as an example. They can give ideas about how you can use sub-query, self-join, or inline table alias to get the desired data. You can modify them to get the 3rd, 4th, 5th highest salary and so on. There could be more ways to get the second-highest salary. I have given 7 of them here. Hope you will find the queries useful.

More Topics