How to find second highest or maximum salary of Employee in SQL

In this section we will write SQL query to get second highest salary of Employee. Before writing query its good to be familiar with schema as well as data in table. Here is the Employee table we will be using this SQL example:


mysql>SELECT *FROM Employee;

+--------+----------+---------+--------+
| emp_id |emp_name | dept_id |salary |
+--------+----------+---------+--------+
| 1     | James    | 10      |   2000 |
| 2     | Jack     | 10      |   4000 |
| 3     | Henry    | 11      |   6000 |
| 4     | Tom      | 11     |   8000 |
+--------+----------+---------+--------+
 
4 rows INSET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

mysql>SELECT max(salary) FROMEmployee WHERE salary NOT IN(SELECTmax(salary)FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
1 row INSET (0.00 sec)

If you look data, you will find that second maximum salary in this case is 6000 and employee name is Henry. Now let’s see some SQL example to find out this second maximum salary.
Sub queries in SQL are great tool for this kind of scenario, here we first select maximum salary and then another maximum excluding result of subquery. To learn more about Subquery see correlate and non-correlate subquery in SQL

 mysql>SELECT max(salary) FROMEmployee WHERE salary NOT IN(SELECTmax(salary)FROM Employee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row INSET (0.00 sec)

Here is another SQL query to find second highest salary using subquery and < operator instead of IN clause:

mysql>SELECT max(salary) FROMEmployee WHERE salary < (SELECT max(salary) FROMEmployee);

+-------------+
| max(salary) |
+-------------+
|        6000 |
+-------------+
 
1 row INSET (0.00 sec)

TOP keyword of Sybase and SQL Server database is used to select top record or row of any result set, by carefully using TOP keyword you can find out second maximum or Nth maximum salary as shown below.

SELECT TOP 1salary FROM (SELECT TOP 2salary FROM employees ORDER BYsalary DESC)AS emp ORDERBY salary ASC

Here is what this SQL query is doing : First find out top 2 salary from Employee table and list them in descending order, Now second highest salary of employee is at top so just take that value. Though you need to keep in mind of using distinct keyword if there are more than one employee with top salary, because in that case same salary will be repeated and TOP 2 may list same salary twice.

Second maximum salary using LIMIT keyword of MYSQL database

LIMIT keyword of MySQL database is little bit similar with TOP keyword of SQL Server database and allows to take only certain rows from result set. If you look at below SQL example, its very much similar to SQL Server TOP keyword example.

mysql>SELECT salary  FROM (SELECT salary FROMEmployee ORDER BY salary DESCLIMIT 2) AS emp ORDER BYsalary LIMIT 1;

+--------+
| salary |
+--------+
|   6000|
+--------+
 
1 row INSET (0.00 sec)