Abstract / Summary

Find the second highest salary from a list of employee salaries.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 48% (3+ million submissions).

Typical patterns to solve this problem:

• Database

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The statement retrieves the second-highest distinct salary from the Employee table. It does this by first collapsing duplicate salary values, ordering the distinct salaries in descending order, and then selecting the row that appears in the second position. The outer SELECT MAX(salary) is used as a safety net: if the inner query yields no rows (for example when the table is empty or contains only one distinct salary), the result is NULL, which matches the comment in the code.

Key Steps

Step 1 - Produce a list of distinct salaries

SELECT salary
FROM Employee
GROUP BY salary

GROUP BY salary eliminates duplicate salary entries, ensuring that each salary appears only once in the subsequent ordering phase. This step guarantees that the “second highest” refers to a distinct value rather than the second row in an unsanitized list.

Step 2 - Order salaries from highest to lowest

ORDER BY salary DESC

Sorting in descending order places the greatest salary at the top of the result set. The ordering is performed after the grouping, so the sort operates on a reduced dataset, which reduces the amount of work compared with sorting the entire original table.

Step 3 - Skip the highest salary and fetch the next one

LIMIT 1, 1

The LIMIT clause with an offset of 1 skips the first row (the highest salary) and then returns exactly one row, which is the second highest distinct salary. If the dataset contains fewer than two distinct salaries, this clause produces an empty result set.

Step 4 - Wrap the inner query and apply MAX

SELECT MAX(salary) AS SecondHighestSalary
FROM ( ... ) s

The outer query treats the single-row result of the inner query as a derived table s. Applying MAX to this single column yields the same value, but more importantly, when the inner query returns no rows, MAX returns NULL. This behavior satisfies the requirement that the query should return NULL when a second highest salary does not exist.

Edge Cases Handled

When the Employee table is empty, the inner query produces no rows, and the outer MAX returns NULL. The same NULL result occurs when there is exactly one distinct salary in the table, because the LIMIT 1, 1 offset cannot be satisfied. Duplicate salary values are collapsed by the GROUP BY, so a table containing many rows with the same highest salary still yields a correct second distinct salary, or NULL if no other distinct value exists.

Performance Optimizations

The use of GROUP BY salary before sorting reduces the number of rows that need to be ordered, which can be significant when the Employee table contains many duplicate salary entries. By limiting the result set to a single row with LIMIT 1, 1, the database engine can stop processing as soon as it has identified the second distinct salary, avoiding a full scan of the sorted list. The outer MAX operates on at most one row, incurring negligible overhead. Together, these choices keep the execution work proportional to the number of distinct salaries rather than the total number of employee records.

Imagine a big room filled with filing cabinets that hold all the details about the people who work at our company. Every person has a piece of paper that says how much money they earn. This code is like a little robot helper that goes into that room to find a specific number. First, the robot gathers all the different salary papers and puts them in a pile, making sure to remove any duplicates so each amount is only counted once. Then, it sorts those papers from the largest amount all the way down to the smallest amount. After the papers are in order, the robot skips the very first paper, which is the biggest one, and walks over to the next one in line. It picks up that second paper and reads the number written on it, because that is the second highest salary we are looking for today.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

Time: loglinear
As a rule of thumb, the maximum input size for one second execution time is

• factorial: up to 10 elements
• exponential: up to 20 elements
• polynomial: a few thousand elements
• loglinear: up to 100,000 elements
• linear: a few million elements
• logarithmic: a billion elements
• constant: practically unlimited

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		Python	C++	Java	TS
	WalkCCC	SQL
	leetcode.ca	SQL	Python
	LeetcodeHelp		Python	C++	Java
	Progiez		Python	C++	Java
github	everthis	SQL
	fsq	SQL
	gaosq0604		Python
	lzl124631x	SQL
	suxrobGM	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	Python, SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
fsq	SQL
gaosq0604	Python
lzl124631x	SQL
suxrobGM	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality