Leetcode SQL 50 -- 176. Second Highest Salary

LeetCode > SQL_50 SQL

Created At : 2024-10-30 06:49

Count:279 Views :

Question
1. Table name: ``
Explaination
1. 1. Using LIMIT and OFFSET
2. 2. Recursive SELECT
Solution
1. Using LIMIT and OFFSET
2. Recursive SELECT

Question

Table name: ``

Column Name	Type
id	int
salary	int

Each row of this table contains information about the salary of an employee.

Write a solution to find the second highest distinct salary from the Employee table.
If there is no second highest salary, return null.

Explaination

The key idea of this question is to get the second value. Here I have two ideas that come to my mind immediately

First, order the list according to salary, then fetch the second row.
First, find the highest salary, then do another SELECT query based on the highest salary.

1. Using LIMIT and OFFSET

Using LIMIT and OFFSET, we can simply retrieve the second row of a database:

SELECT DISTINCT Salary 
    FROM Employee 
    ORDER BY salary DESC 
    LIMIT 1 OFFSET 1

Here, we used ORDER BY salary DESC and DISTINCT to make sure the second row contains the second highest salary.
Then LIMIT 1 constraints there will be only one row returned, OFFSET 1 means to skip 1 row before fetching.

However, this query won’t return null if there isn’t a second row.

To solve the problem, we can wrap this query by another SELECT query.

2. Recursive SELECT

There will be two SELECT function, the SELECT in WHERE clause returns the highest salary:

WHERE salary < (SELECT MAX(salary) FROM Employee)

So that the SELECT query outside can directly compare with this and thus it returns the second highest salary.

Solution

Using LIMIT and OFFSET

SELECT(
    SELECT DISTINCT Salary 
    FROM Employee 
    ORDER BY salary DESC 
    LIMIT 1 OFFSET 1 )
AS SecondHighestSalary;

Recursive SELECT

SELECT MAX(salary) AS SecondHighestSalary 
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);