Second Highest Salary in MySQL and SQL Server - LeetCode Solution ~ GNIIT Solutions

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 database 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 second highest salary and you must know couple of them e.g. in MySQL without using LIMITkeyword, in SQL Server without using TOP and in Oracle without using RANK and ROWNUM. Once you solve the problem, Interviewer will most likely increase the difficulty level by either moving to Nth salary direction or taking away this buit-in utilities.

Second Highest Salary in MySQL without LIMIT

Here is a generic SQL query to find second highest salary, which will also work fine in MySQL. Thissolution uses subquery to first exclude the maximum salary from the data set and then again finds 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 which 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 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 correlatedsubquery. This is also generic solution to find Nth highest salary in 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 difference between correlated and non-correlated sub-query, 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 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 form Employee table in decreasing order, so that 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 on ascending order, so that second highest salary comes at 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 alarge 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 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 :

Write SQL query to get third highest salary from Employee table?
How do you find 4th highest salary in MySQL without using LIMIT keyword?
Write SQL query to find second highest salary in Oracle database using ROWNUM?
How to find Nth highest salary in SQL Server without using TOP keyword?
Find second highest salary in Oracle using rank?
How to find top 3 salary in Oracle without using ROW_NUMBER or RANK()?

That's all about different ways to find Second highest Salary in MySQL and SQL Server. We have seen examples to get second highest salary in MySQL by using LIMIT and without using LIMIT. Similarly in MSSQL we know how to get second highest salary by using TOP and without using TOP keyword. I have left the Oracle database for you as an exercise. If you able to find solution of all above SQL queries in quick time and feeling bore again, checkout my post about Top 20 SQL queries from Interviews for some more fun.

Shop Related Products