Thursday, July 13, 2023

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

How to find the second highest or second maximum salary of an Employee is one of the most frequently asked SQL interview questions 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 the 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 queries to get the second highest salary of Employees. Before writing query its good to be familiar with the 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)

If you look at data, you will find that the second maximum salary, in this case, is 6000, and the employee name is Henry. 

Now let’s see some SQL examples to find out this second maximum salary.




Second maximum salary using subquery 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)

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)

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.

how to find second highest salary in SQL




Second highest salary using the 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 highest salary using the TOP keyword of Sybase or SQL Server database



Second maximum salary using LIMIT keyword of MYSQL database

SQL Query to find second highest or maximum salary of EmployeeLIMIT 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)


That’s on How to find the 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


26 comments:

  1. @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;

    ReplyDelete
  2. @Gaurav, Thanks. Again use of distinct keyword to only select unique salaries may or may not require based on problem statement.

    ReplyDelete
  3. 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 ?

    ReplyDelete
  4. 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

    ReplyDelete
  5. For Oracle

    select * FROM (select salary , rownum as RN from dbo.Employee order by salary desc) table1 where RN=2

    ReplyDelete
    Replies
    1. I don't think u can write rownum=2 there
      Acc to me solution can be
      Select min(sal) from (Select sal from Emp order by sal desc)
      where rownum < 3;

      Delete
  6. select max(salary) from employee where salary <any(select salary from employee)

    ReplyDelete
  7. how about this?
    Select salary from Employee order by salary desc limit 1,1;

    ReplyDelete
  8. /*for 3rd highest salary n=2, for 2nd highest salary n=1, nth highest salary n=n-1*/

    SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT n,1

    ReplyDelete
  9. How to write SQL query for third highest salary?

    ReplyDelete
  10. SELECT TOP 1 salary FROM ( SELECT TOP 3 salary FROM employees ORDER BY salary DESC) AS emp ORDER BY salary ASC

    Is this correct for 3rd highest salary ??

    ReplyDelete
  11. select * from employees where row_num=2 order by desc.
    Is it correct in oracle. I dont have oracle data base. can some body tell me

    ReplyDelete
  12. SELECT salary FROM employee ORDER BY salary DESC LIMIT 1 , 1

    ReplyDelete
  13. If you are looking for a pure sql query to find the nth highest salary, I found out this one to be the best
    SELECT * FROM Employee Emp1 WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary))
    FROM Employee Emp2
    WHERE Emp2.Salary > Emp1.Salary)

    ReplyDelete
  14. ----is this correct way to find third highest salary
    SELECT ENAME,SALARY,DEPTNO
    FROM EMPLOYEE O
    WHERE 3=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEE P
    WHERE O.SALARY<=P.SALARY)

    ReplyDelete
  15. @Murali, that's the perfect way to find the third highest salary. In fact, your solution uses correlated subquery and much more generic than normal solution without subquery, because you can just change the number near to WHERE clause e.g. WHERE 3 = (correlated query) to get third maximum, WHERE 4= () to get fourth largest and WHERE N = () to get Nth highest salary of employee.

    But before using this query, one should understand how exactly it work, for each row processed by outer query, inner query will also be executed. So in case if the current record is for third highest salary then inner query will return how many rows which has salary greater than current one, if it is third highest then obviously there will be three rows.

    ReplyDelete
    Replies
    1. @sarvathi can u please update complete query, to find the nth highest salary

      Delete
  16. @Gunjan . Your solution is really good. Can you elaborate your solution with explanation.

    ReplyDelete
  17. how to find middle five eraners in salary from using of subquery

    ReplyDelete
  18. select top 1 b.* from employee a left join employee b
    on a.salary > b.salary
    order by salary desc

    ReplyDelete
  19. Best Solution ...
    SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2 ,1;

    Read more: http://code.shouttoday.com

    ReplyDelete
  20. Well, it's best if you are using MySQL because LIMIT is not going to work on SQL Server, Oracle, or PostgreSQL.

    ReplyDelete
  21. For Nth max...
    SELECT max(salary) from Employee e1
    WHERE N=select count(e2. Salary) from Employee e2 WHERE e1.salary <= e2. Salary

    ReplyDelete
  22. SELECT *
    FROM EMPLOYEES
    ORDER BY SALARY DESC
    OFFSET 1 ROWS
    FETCH NEXT 1 ROW ONLY

    ReplyDelete
  23. Fourth highest Salary

    create table Salary
    (Name varchar(50) not null primary key,
    Salaries int)

    insert into Salary
    values('abc', 10000),
    ('bcd', 1000000),
    ('efg', 4000),
    ('ghi', 50000)

    select Top 1 Salaries from Salary
    where Salaries IN
    (Select Top 4 Salaries from Salary Order by Salaries Desc)
    Order by Salaries ASC

    This works fine. For Second highest Salary change Top N in Subquery.

    ReplyDelete