Employee is one of the most frequently asked SQL
interview question similar to
finding duplicate records in table and
when to use truncate vs delete. There are many ways to find second highest salary based upon which
database you are using as different database provides different feature which can be used to find
second maximum or
Nth maximum salary of employee. Well this question can also be generalized with other scenario like finding second maximum age etc. In this SQL
tutorial we will see different example of
SELECT SQL query to find second highest salary independent of
databases or you may call in ANSI SQL and other SQL queries which uses database specific feature to find second maximum salary.
SQL query to find second maximum salary of Employee
![SQL Query to find second highest or maximum salary of Employee](http://1.bp.blogspot.com/-ALTiC41IG4I/TZ_n2bwh6iI/AAAAAAAAAFE/Jy4cmcZqUG4/s1600/mysql.gif)
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 )
+ --------+----------+---------+--------+
| 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.
Second maximum salary using sub query and IN clause
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
)
FROM Employee
WHERE salary
NOT IN
(
SELECT max
(salary
)
FROM Employee
);
+ -------------+
| max (salary ) |
+ -------------+
| 6000 |
+ -------------+
1 row INSET ( 0.00 sec )
+ -------------+
| 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
)
FROM Employee
WHERE salary
<
(
SELECT max
(salary
)
FROM Employee
);
+ -------------+
| max (salary ) |
+ -------------+
| 6000 |
+ -------------+
1 row INSET ( 0.00 sec )
+ -------------+
| max (salary ) |
+ -------------+
| 6000 |
+ -------------+
1 row INSET ( 0.00 sec )
Both of above SQL example will work on all database including Oracle, MySQL, Sybase and SQL
Server as they are written using standard SQL keywords. But sometime you can also use database specific features like TOP keyword of SQL Server or Sybase database to find out second highest salary of Employee.
Second highest salary using TOP keyword of Sybase or SQL Server database
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
1 salary
FROM
(
SELECT TOP
2 salary
FROM
employees
ORDER BY salary
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
FROM Employee
ORDERBY salary
DESCLIMIT
2
)
AS emp
ORDERBY salary
LIMIT
1;
+ --------+
| salary |
+ --------+
| 6000 |
+ --------+
1 row INSET ( 0.00 sec )
+ --------+
| salary |
+ --------+
| 6000 |
+ --------+
1 row INSET ( 0.00 sec )
That’s on How to find second highest salary of Employee using SQL query. This is good question which really test your SQL knowledge, its not tough but definitely tricky for
beginners. As follow up question you can ask him to find third maximum salary or Nth maximum salary as well.
Other
SQL Interview Question answers you may like
Read more: http://javarevisited.blogspot.com/2012/12/how-to-find-second-highest-or-maximum-salary-sql.html#ixzz2khph0MQr