Second Highest Salary Inward Mysql Too Sql Server - Leetcode Solution

Write a SQL inquiry to acquire the 2d highest salary from the Employee table.

+----+--------+
| Id | Salary |
+----+--------+
| i  | 100    |
| 2  | 200    |
| iii  | 300    |
+----+--------+

For example, given the higher upwardly Employee table, the 2d highest salary is 200. If in that place is no 2d highest salary, together with thus the inquiry should render NULL. You tin write SQL inquiry inward whatever of your favorite database e.g. MySQL, Microsoft SQL Server or Oracle. You tin also role database specific characteristic e.g. TOP, LIMIT or ROW_NUMBER to write SQL query, but you lot must also render a generic solution which should operate on all database. In fact, in that place are several ways to uncovering 2d highest salary together with you lot must know twain of them e.g. inward MySQL without using LIMIT keyword, inward SQL Server without using TOP together with inward Oracle without using RANK together with ROWNUM. Once you lot solve the problem, Interviewer volition most probable increase the difficulty degree past times either moving to Nth salary administration or taking away this buit-in utilities.

Second Highest Salary inward MySQL without LIMIT

Here is a generic SQL inquiry to uncovering 2d highest salary, which volition also operate fine inward MySQL. This solution uses subquery to start exclude the maximum salary from the information fix together with and thus over again finds maximum salary, which is effectively the 2d maximum salary from the Employee table.

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

This volition render 200 inward our case.

Here is to a greater extent than or less other solution which uses sub inquiry but instead of IN clause it uses < operator

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

You tin role this SQL inquiry if Interviewer inquire you lot to acquire 2d highest salary inward MySQL without using LIMIT.  You tin also role distinct keyword if your Employee tabular array may incorporate duplicate salary, In this instance in that place is no such record, thus I accept non used distinct.

Second Highest Salary using Correlated SubQuery

Previous SQL inquiry was also using subquery but it was non-correlated, this solution volition role correlated subquery. This is also generic solution to find Nth highest salary inward Employee table. For each tape processed past times outer query, inner inquiry volition last executed together with volition render how many records has records has salary less than the electrical current salary. If you lot are looking for 2d highest salary together with thus your inquiry volition halt equally before long equally inner inquiry volition render 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 lot don't know divergence betwixt correlated together with non-correlated sub-query, run across here.

Second Maximum Salary inward MySQL using LIMIT

MySQL has a particular keyword called LIMIT which tin last used to limit the outcome fix e.g. it volition allow you lot to run across start few rows, terminal few rows or hit of rows. You tin role this keyword to uncovering the second, 3rd or Nth highest salary. Just role social club past times clause to kind the outcome fix together with thus impress the 2d salary equally 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, nosotros accept start sorted all salaries cast Employee tabular array inward decreasing order, thus that 2 highest salaries come upwardly at tumble out of the outcome set. After that nosotros took merely 2 records past times using LIMIT 2. Again nosotros did the same affair but this fourth dimension nosotros kind the outcome assail ascending order, thus that 2d highest salary comes at top. Now nosotros impress that salary past times using LIMIT 1. Simple together with easy, right?

Second Highest Salary using SQL Server Top Keyword

Just similar MySQL has LIMIT keyword, which is immensely helpful inward sorting together with paging, Microsoft SQL Server also has a particular keyword called TOP, which equally refer propose prints tumble out records from outcome set. You tin impress tumble out 10 records past times maxim TOP 10. I oftentimes role this keyword to run across the information from a large table, merely to sympathise columns together with information within it. Here is the SQL inquiry to uncovering 2d maximum salary inward 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 higher upwardly inquiry running on Microsoft SQL Server 2014 :

Now It's fourth dimension to apply the cognition you lot accept learned thus far. Solve next SQL queries at your convenience :

1. Write SQL inquiry to acquire 3rd highest salary from Employee table?
2. How create you lot uncovering fourth highest salary inward MySQL without using LIMIT keyword?
3. Write SQL inquiry to uncovering 2d highest salary inward Oracle database using ROWNUM?
4. How to uncovering Nth highest salary inward SQL Server without using TOP keyword?
5. Find 2d highest salary inward Oracle using rank?
6. How to uncovering tumble out iii salary inward Oracle without using ROW_NUMBER or RANK()?

That's all well-nigh different ways to uncovering Second highest Salary inward MySQL together with SQL Server.  We accept seen examples to acquire 2d highest salary inward MySQL past times using LIMIT together with without using LIMIT. Similarly inward MSSQL nosotros know how to acquire 2d highest salary past times using TOP together with without using TOP keyword. I accept left the Oracle database for you lot equally an exercise. If you lot able to uncovering solution of all higher upwardly SQL queries inward quick fourth dimension together with feeling bore again, checkout my post well-nigh Top xx SQL queries from Interviews for to a greater extent than or less to a greater extent than fun.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners