MediumSubqueries

Second Highest Salary

Q: Is LIMIT 1, 1 the same as LIMIT 1 OFFSET 1?

Yes. MySQL accepts both spellings; LIMIT offset, count is the older one. LIMIT 1 OFFSET 1 reads more clearly and is what PostgreSQL and SQLite use too.

Q: Can I use IFNULL to force the NULL row?

You can wrap the subquery, SELECT IFNULL((SELECT …), NULL), but it adds nothing: the scalar subquery already yields NULL when it finds no row. IFNULL is useful when you want a different default, such as 0.

Q: Does the MAX version need DISTINCT?

No. salary < (SELECT MAX(salary) FROM employee) excludes every copy of the top salary, and MAX of what is left is a single value whether or not it repeats.

The employee table has one row per employee: an id and a salary. Write a query that returns the second highest distinct salary, in a column named SecondHighestSalary. Two employees on the same salary count as one salary. When there is no second distinct salary, because every employee earns the same or the table has one row, return a single row whose value is null, not an empty result.

Examples

Input

employee

id	salary
1	100
2	200
3	300

Output

SecondHighestSalary
200

WhyThe distinct salaries are 100, 200 and 300; the second highest is 200.

Input

employee

id	salary
1	100

Output

SecondHighestSalary
null

WhyOne employee means one salary, so there is no second one: one row with null.

Constraints

Rows: 0 ≤ rows ≤ 1,000
Salary: -10⁶ ≤ salary ≤ 10⁶
Result: Exactly one row, one column named SecondHighestSalary.
Time limit: 10 s per run, all cases

Run your code 2 more times to open the solutions. The idea sticks better after an attempt.

FAQ for MySQL

Why does the plain LIMIT 1 OFFSET 1 query return an empty set?

Skipping past the last row leaves no rows, and an empty result is not what the problem wants. Putting the query in the select list as a scalar subquery makes the outer query produce one row, and the subquery's missing value becomes NULL.

Is LIMIT 1, 1 the same as LIMIT 1 OFFSET 1?

Yes. MySQL accepts both spellings; LIMIT offset, count is the older one. LIMIT 1 OFFSET 1 reads more clearly and is what PostgreSQL and SQLite use too.

Can I use IFNULL to force the NULL row?

You can wrap the subquery, SELECT IFNULL((SELECT …), NULL), but it adds nothing: the scalar subquery already yields NULL when it finds no row. IFNULL is useful when you want a different default, such as 0.

Does the MAX version need DISTINCT?

No. salary < (SELECT MAX(salary) FROM employee) excludes every copy of the top salary, and MAX of what is left is a single value whether or not it repeats.

Keep going

Try Second Highest Salary in SQLite (Second Highest Salary in SQLite), PostgreSQL (Second Highest Salary in PostgreSQL), MariaDB (Second Highest Salary in MariaDB), T-SQL (Second Highest Salary in T-SQL), DuckDB (Second Highest Salary in DuckDB) or pandas (Second Highest Salary in pandas) instead.

second_highest_salary.sql

MySQL 8.4

Not run yet

+ 5 hidden

Input

employee
id  salary
1   100
2   200
3   300

Expected

[{"SecondHighestSalary": 200}]

The distinct salaries are 100, 200 and 300; the second highest is 200.

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first

1 · MAX of the salaries below the maximum

2 · Distinct salaries sorted, skip the first