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 the nth-highest salary using SQL". We will use phpMyAdmin SQL window to write and execute the query. We will use employee salary as an example; however, you can use these queries to extract similar data from the other tables by changing the table and column names.

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:

CREATE TABLE `employees` (
  `id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `employees` (`id`, `emp_id`, `name`, `email`, `gender`, `salary`) VALUES
(1, 2230, 'John Doe', 'jdoe@test.com', 'M', 20000),
(2, 2624, 'Robert S', 'roberts@test.com', 'M', 10000),
(3, 9654, 'Joe Stewart', 'jstewart@test.com', 'M', 40000),
(4, 2262, 'Julie Agarwal', 'sagarwal@test.com', 'F', 25000),
(5, 2471, 'Amanda J', 'amandaj@test.com', 'F', 18000);

ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `employees`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

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

Table data:

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

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 the LIMIT clause in the SQL statement.

Query 1:

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

SELECT emp_id, name, email, gender, salary
FROM employees
WHERE salary=(SELECT
	MAX(salary)
	FROM employees
	WHERE salary < (
		SELECT MAX(salary)
		FROM employees));

Output:

If you want to get only 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.

SELECT emp_id, name, email, gender, salary
FROM employees
WHERE salary=(SELECT
	MAX(salary)
	FROM employees
	WHERE salary NOT IN
		(SELECT MAX(salary)
		FROM employees))

Output:

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

Query 3:

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

SELECT emp_id, name, email, gender, salary
FROM employees A
WHERE 2 = (SELECT COUNT(salary) 
	  FROM employees B
	  WHERE A.salary <= B.salary);

Output:

Query 4:

This query uses the LIMIT clause in the SQL statement.

SELECT emp_id, name, email, gender, salary
FROM employees
WHERE salary < (SELECT
	MAX(salary)
	FROM employees)
ORDER BY salary DESC
LIMIT 1;

Output:

Query 5:

This query uses the LIMIT and OFFSET clauses in the SQL statement.

SELECT emp_id, name, email, gender, salary
FROM employees 
ORDER BY salary DESC LIMIT 1,1; 

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

Output:

Query 6:

This query uses the LIMIT clause in the SQL statement.

SELECT emp_id, name, email, gender, salary 
FROM employees 
WHERE salary = (SELECT MIN(salary) 
		FROM (SELECT salary FROM employees 
		ORDER BY salary DESC LIMIT 2) 
	AS emp)

For the n^th highest salary use LIMIT n in the above query. So, for the 3rd highest use LIMIT 3.

Output:

Query 7:

This query also uses the LIMIT clause in the SQL statement.

SELECT emp_id, name, email, gender, salary 
FROM (SELECT * FROM employees 
	ORDER BY salary DESC LIMIT 2) AS emp 
ORDER BY salary LIMIT 1;

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

Output:

Conclusion

I have given a few example SQL statements here to get the second-highest salary as an example. They give an idea about how to 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. I hope you will find the queries useful.