Second Highest Salary in MySQL and SQL Server - LeetCode Solution

Write a SQL query to get the second highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

For example, given the above Employee table, the second highest salary is 200. If there is no second highest salary, then the query should return NULL. You can write SQL query in any of your favorite databases e.g. MySQL, Microsoft SQL Server, or Oracle. You can also use database specific feature e.g. TOP, LIMIT or ROW_NUMBER to write SQL query, but you must also provide a generic solution which should work on all database. 

In fact, there are several ways to find the second highest salary and you must know a couple of them e.g. in MySQL without using the LIMIT keyword, in SQL Server without using TOP, and in Oracle without using RANK and ROWNUM

Once you solve the problem, the Interviewer will most likely increase the difficulty level by either moving to the Nth salary direction or taking away these built-in utilities.



Second Highest Salary in MySQL without LIMIT

Here is a generic SQL query to find the second highest salary, which will also work fine in MySQL. This solution uses a subquery to first exclude the maximum salary from the data set and then again finds the maximum salary, which is effectively the second maximum salary from the Employee table.

SELECT MAX(salary) FROM Employee 
WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

This will return 200 in our case.

Here is another solution that uses sub query but instead of IN clause it uses < operator

SELECT MAX(Salary) From Employee
 WHERE Salary < ( SELECT Max(Salary) FROM Employee);

You can use this SQL query if the Interviewer ask you to get second highest salary in MySQL without using LIMIT.  You can also use distinct keyword if your Employee table may contain duplicate salary, In this example there is no such record, so I have not used distinct.




Second Highest Salary using Correlated SubQuery

Previous SQL query was also using subquery but it was non-correlated, this solution will use a correlated subquery. This is also a generic solution to find Nth highest salary in the Employee table

For each record processed by outer query, inner query will be executed and will return how many records has records has salary less than the current salary. If you are looking for second highest salary then your query will stop as soon as inner query will return 2.

SELECT Id, Salary
FROM Employee e
WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p
WHERE e.Salary<=p.Salary)


By the way, If you don't know the difference between correlated and non-correlated subquery, see here.




Second Maximum Salary in MySQL using LIMIT

MySQL has a special keyword called LIMIT which can be used to limit the result set e.g. it will allow you to see the first few rows, last few rows, or range of rows. You can use this keyword to find the second, third or Nth highest salary. Just use order by clause to sort the result set then print the second salary as shown below :

SELECT Salary FROM 
  (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp 
ORDER BY salary LIMIT 1;

In this solution, we have first sorted all salaries from the Employee table in decreasing order, so that the 2 highest salaries come at top of the result set. After that, we took just two records by using LIMIT 2. 

Again we did the same thing but this time we sort the result set in ascending order so that the second-highest salary comes at the top. Now we print that salary by using LIMIT 1. Simple and easy, right?




Second Highest Salary using SQL Server Top Keyword

Just like MySQL has LIMIT keyword, which is immensely helpful in sorting and paging, Microsoft SQL Server also has a special keyword called TOP, which as name suggest prints top records from result set. You can print top 10 records by saying TOP 10. 

I frequently use this keyword to see the data from a large table, just to understand columns and data inside it. Here is the SQL query to find second maximum salary in SQL Server :

SELECT TOP 1 Salary FROM 
( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable 
ORDER BY Salary ASC;

Here is the output of the above query running on Microsoft SQL Server 2014 :
Second Highest Salary in MySQL and MSSQL













Now It's time to apply the knowledge you have learned so far. Solve following SQL queries at your convenience :
  1. Write SQL query to get a third highest salary from Employee table?
  2. How do you find the 4th highest salary in MySQL without using the LIMIT keyword?
  3. Write SQL query to find second highest salary in Oracle database using ROWNUM?
  4. How to find Nth highest salary in SQL Server without using TOP keyword?
  5. Find the second highest salary in Oracle using rank?
  6. How to find the top 3 salaries in Oracle without using ROW_NUMBER or RANK()?
Now, let's see the answers for few questions

1. Write SQL query to get a third highest salary from Employee table?
To retrieve the third-highest salary from the Employee table, you can use the following SQL query:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 2;

This query selects distinct salary values from the Employee table, orders them in descending order (highest to lowest), and then uses the LIMIT and OFFSET clauses to skip the first two rows and retrieve the third-highest salary. Adjust the table and column names according to your actual database schema.

2. How do you find the 4th highest salary in MySQL without using the LIMIT keyword?
To find the 4th highest salary in MySQL without using the LIMIT keyword, you can use a subquery with the DISTINCT keyword along with the ORDER BY clause. Here's an example SQL query:

SELECT MAX(Salary) AS FourthHighestSalary
FROM Employee
WHERE Salary NOT IN (
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 3
);

This query uses a subquery to get the top three distinct salary values, and then filters out those salaries in the outer query, effectively retrieving the fourth highest salary. Make sure to replace Employee with the actual table name and adjust column names accordingly based on your database schema.

That's all about different ways to find the Second highest Salary in MySQL and SQL Server.  We have seen examples to get the second highest salary in MySQL by using LIMIT and without using LIMIT. Similarly in MSSQL, we know how to get the second highest salary by using TOP and without using the TOP keyword. 

I have left the Oracle database for you as an exercise. If you are able to find solutions to all the above SQL queries in a quick time and feeling bore again, check out my post about the Top 20 SQL queries from Interviews for some more fun.


49 comments:

  1. You can easily solve this problem by using window function in SQL Server 2014 e.g. RANK() and ROW_NUMBER(). This is in fact perfect example of when to use window function in MSSQL.

    Here is how to find second highest salary using RANK() function in SQL Server 2014

    select sr.* from (select [id], salary, rank() over (order by salary desc) as salary_rank from salaries) sr where sr.salary_rank = 2;

    ReplyDelete
    Replies
    1. Try This:
      select salary from employee order by salary desc limit 1 offset 1;

      Delete
    2. This is as easy as it can be but it will not work in all database and probably not on interviews, where you are most likely be asked about window function and co-related subquery. It's good to know those techniques as well.

      Delete
  2. Select min(salary) from ( select top2 salary from employee order by desc ) as salay; ;)

    ReplyDelete
  3. Select min(salary) from ( select top2 salary from employee order by desc ) as salay; ;)

    ReplyDelete
    Replies
    1. what if first twosalary is same then this qunry work ?

      Delete
    2. If criterion is to choose unique salary then just use distinct to filter out duplicate salaries like
      Select min(salary) from ( select top2 distinct salary from employee order by desc ) as salay;

      Delete
  4. select * from (select salary, dense_rank()over(order by salary desc)rk from emp)
    Where rk=2;

    ReplyDelete
  5. THIRD Highest Salary:--
    select MAX(Salary) from EmpSalary where salary <(select MAX(Salary) from EmpSalary where salary <(select MAX(Salary) from EmpSalary))

    ReplyDelete
    Replies
    1. SQL> select max(sal) from emp
      2 where sal > ( select max(sal) from emp where sal > (select max(sal) from emp));

      MAX(SAL)
      ----------


      NOT WORKING

      Delete
    2. select top 1 salary from Mahesh where salary in
      (select distinct top 3 salary from Mahesh order by salary desc)
      order by salary

      Delete
  6. WE CAN GET N th SALARY FROM A TABLE

    IF ANY DUPLICATES SALARIES IT CAN WORK

    SELECT SALARY FROM WHERE SALARY=(
    SELECT MIN(SALARY)FROM (SELECT TOP N SALARY FROM ORDER BY DESC
    )T)

    IN THIS CASE N=2

    THIS IS SAGAR

    WEB SYNERGIES

    ReplyDelete
  7. Hi ,
    For the below query :
    SELECT Id, Salary FROM Employee e WHERE 2=(SELECT COUNT(DISTINCT Salary) FROM Employee p WHERE e.Salary<=p.Salary)

    Shouldn't the e.salary<=p.salary condition in the subquery reversed . We are comparing the p.salary column with the e.salary column from the outer query repeatedly. Please let me know if my understanding is wrong .

    ReplyDelete
  8. we cant use where for aggregate but how goe you are getting solution iam unable to get the solution and its giving the above error

    ReplyDelete
  9. select min(emp_salary)
    from (select distinct * from employee_table
    order by emp_salary desc
    limit 2);
    this query uses a sub-query to find out the first two highest salaries of employees in employee_table relation (note that the value of limit is 2), now using these two records it finds the minimum of both, which would be the second highest salary.
    ---------------------------------------------------------------------------------------
    this query finds the second lowest salary and works pretty much the same way as the above do ->
    select max(emp_salary)
    from (select distinct * from employee_table
    order by emp_salary asc
    limit 2);

    ReplyDelete
  10. select * from max(sal) from emp1 where sal<(select max(sal) from emp1)

    ReplyDelete
  11. Make it little complicate, suppose you have 4 duplicate rows of 2 IDs in same numbers then RANK/ROW_NUMBER function will not work as I tried.

    That time I got the above desired output just by through the correlated subquery and operator only. (In SQL Server)

    ReplyDelete
  12. Write SQL query to get third highest salary from Employee table?

    Solution: Select salary from employee order by salary DESC limit 3,1;

    ReplyDelete
  13. select * from (
    select e.*, row_number() over (order by e.id desc) rn from emp e
    )
    where rn = 3;

    ReplyDelete
  14. THANKS VERY HELPFUL :-)

    ReplyDelete
  15. SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary DESC LIMIT 2) AS Emp ORDER BY salary LIMIT 1;
    The above works for your case but will not work always it should ideally be
    SELECT Salary FROM (SELECT Salary FROM Employee ORDER BY salary ASC LIMIT 2) AS Emp ORDER BY salary DESC LIMIT 1;

    ReplyDelete
    Replies
    1. from the above query it will provide the result of highest salary of employee instead
      of second highest salary

      Delete
  16. Use below query to find nth highest salary:
    select salary from employee order by salary desc limit N-1,1;

    ReplyDelete
  17. select top 1 RoleId from (select top 3 RoleId from RoleMaster order by RoleId asc) as result order by RoleId desc

    ReplyDelete
  18. A more complex way in MySQL using session variable
    SET @rank := 0;
    SET @last_value := 0;
    SELECT * FROM (
    SELECT id, salary, IF(@last_value = salary, @rank, @rank := @rank + 1) AS rank, @last_value, @last_value := salary FROM key_value ORDER BY salary DESC)
    AS temp WHERE rank = 3;

    ReplyDelete
  19. SELECT DISTINCT(Salary) FROM Employee ORDER BY Salary DESC LIMIT 1,1;

    ReplyDelete
  20. Select * from Employee order by Salary desc limit 1 offset 1;//here we go

    ReplyDelete
  21. select * from Employee in(select MAX(Salary) form Employee having Salary!=(Select MAX(Salary) from Employee)

    ReplyDelete
  22. SQL> select max(sal) from emp
    2 where sal > ( select max(sal) from emp where sal > (select max(sal) from emp));

    MAX(SAL)
    ----------

    not working

    ReplyDelete
    Replies
    1. Not working on which database? what output/error you are getting?

      Delete
  23. select *
    from employees
    order by salary desc
    limit 1,1;

    ReplyDelete
  24. Write SQL query to get third highest salary from Employee table without using nested query?

    ReplyDelete
  25. Select top 1 a.* from (select Distinct top 2 Salary as slry from employee order by desc)a
    order by a.slry asc

    ReplyDelete
  26. Not valid order by clause in subquery.

    ReplyDelete
  27. if the second maximum salary has two rows say there are two employees having 200
    how can i display the records of both the rows having the same second highest salary

    ReplyDelete
    Replies
    1. just use that salary to get all employees with that salary

      Delete
    2. Use the -- distinct(salary)

      Delete
  28. /*Add 3 months to the employee who joined on 26th feb 2008 and whose sal is 30000*/
    select * from Employee
    where Sal='30000'

    select DATEADD(month,3,'2008-02-26')AS DateAdd;
    Q)how do I add this two statements?

    ReplyDelete
    Replies
    1. May be you can try like this:
      select e.*, DATEADD(month,3,'2008-02-26')AS DateAdd from Employee e where e.Sal='30000'

      Delete
  29. select e.*, DATEADD(month,3,'2008-02-26')AS DateAdd from Employee e where e.Sal='30000'

    ReplyDelete
  30. Thanks, helpful! You saved me some headache.

    ReplyDelete
  31. how to modify it for Nth highest? Tried but getting error.

    ReplyDelete
    Replies
    1. Hello reehant, what error you are getting and what is your SQL query, can you post it here please? I have showed how to find Nth highest salary on this article, you may want to check.

      Delete
  32. How to use in Joins
    display max(sala) based on depart wise
    dep_no, de_name, max(sal)

    ReplyDelete
  33. This query shown all employee list of getting 2nd highest salary.

    select * from master_salary where emp_salary = (select `emp_salary` from `master_salary` group by emp_salary limit 1,1);

    ReplyDelete
  34. What is the table has only one record? How to return null in that case.

    ReplyDelete
    Replies
    1. What is returning in current code? I believe it will return null if table is empty and the one record if table just have one record

      Delete

Feel free to comment, ask questions if you have any doubt.