How to find second highest or second maximum salary of an 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
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 IN SET (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 IN SET (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 IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (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 IN SET (0.00 sec)
+-------------+
| max(salary) |
+-------------+
| 6000 |
+-------------+
1 row IN SET (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 ORDER
BY 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 ORDER BY salary DESC
LIMIT 2) AS emp
ORDER BY
salary LIMIT 1;
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (0.00 sec)
+--------+
| salary |
+--------+
| 6000 |
+--------+
1 row IN SET (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
Posts
6 comments:
@Javin Here you missed to select nth record from a table. Suppose some one need to select 4th highest salary. So in MySql he can write like below
SELECT salary FROM Employee order by salary DESC limit 4,1;
@Gaurav, Thanks. Again use of distinct keyword to only select unique salaries may or may not require based on problem statement.
Does method with TOP keyword to find second highest salary will also work on SQL Server 2005 and SQL Server 2008? I said yes but want to confirm it as I don't have SQL Server 2005 or 2008 installed on my machine to test this SQL query. Also Can you please share How to find Nth maximum salary in Oracle ?
rownum() function is Oracle's equivalent of limit keyword of MySQL and can be used to find second highest, third highest or fourth highest salary or value in Oracle. By using rownum() you can give numbering to each record in resultset and than you can choose top two salaries based on condition in where clause. Here is right way to find Nth maximum salary in Oralce :
select * from (select ROW_NUMBER() over (order by rn desc) as salary from dbo.Employee) as temp where rn =2
This can be used to find 2nd, 3rd, 4th, 5th or any maximum not just salary, any value and it should also work in SQL Server 2008 as it also support ROW_NUMBER() function
For Oracle
select * FROM (select salary , rownum as RN from dbo.Employee order by salary desc) table1 where RN=2
select max(salary) from employee where salary <any(select salary from employee)
Post a Comment