How to Obtain the Second Distinct Highest Record using SQL?


Question: Write a SQL query to get the second highest salary (distinct) 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.

Another example, Input Salary Array = [100, 100], the output should be null; When array equals [100, 100, 90]. The output should be 90 (second highest salary) instead of 100. Therefore the common mistake is the following SQL statement:

1
select * from Employee order by salary desc limit 1,1
select * from Employee order by salary desc limit 1,1

Some might correct this via group by:

1
select Salary as SecondHighestSalary from Employee group by Salary order by Salary limit 1,1 
select Salary as SecondHighestSalary from Employee group by Salary order by Salary limit 1,1 

However, it will return empty instead of null when it does not exist.

Input: {"headers": {"Employee": ["Id", "Salary"]}, "rows": {"Employee": [[1, 100]]}}
Output: {"headers": ["SecondHighestSalary"], "values": []}
Expected: {"headers": ["SecondHighestSalary"], "values": [[null]]}

The correct version should be:

select max(Salary)
from Employee
where Salary < (select max(Salary) from Employee)

The second largest number is always smaller than the largest number. We use a inner SQL to get the maximum salary and we just need to get the largest number that is smaller than this largest number.

That is it, no tricks, no sorting, just two SQL selects and two group functions max

sql How to Obtain the Second Distinct Highest Record using SQL? interviews mysql sql

SQL programming

Algorithms: Second Highest Record

–EOF (The Ultimate Computing & Technology Blog) —

GD Star Rating
loading...
429 words
Last Post: How to Use Array in Windows Batch Programming?
Next Post: C/C++ Coding Exercise - Tiny Echo Program

The Permanent URL is: How to Obtain the Second Distinct Highest Record using SQL?

Leave a Reply