Second Highest Salary

SQL Leet Code Series — Easy (Oraclesql)

SQL Schema

Create table If Not Exists Employee (Id int, Salary int)
Truncate table Employee
insert into Employee (Id, Salary) values ('1', '100')
insert into Employee (Id, Salary) values ('2', '200')
insert into Employee (Id, Salary) values ('3', '300')

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 query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null.

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Approach :

We can use Distinct to get a single unique Value and use the max function in SELECT and use it on WHERE Clause to get our desired output

Logic: First we get all the values/ data in the Salary column that is less than the maximum Salary value then we will select the max out those values

Since using max() will return a NULL it makes our work even easier

Solution:

Note: The solution might slightly differ from DataBase to DataBase the concept and the approach are often the same. Solution for Oracle Sql 11.2.

SELECT DISTINCT
    max(Salary) as SecondHighestSalary
FROM 
    Employee
WHERE 
    Salary < (SELECT
                max(Salary) 
              FROM 
                Employee
             )