MediumSubqueries

Second Highest Salary

Q: Is there a shorter DuckDB-only spelling?

DuckDB allows SELECT max(salary) FILTER (WHERE salary < (SELECT max(salary) FROM employee)) and arg_max, and it has QUALIFY for window filters: SELECT salary FROM (SELECT DISTINCT salary FROM employee) QUALIFY dense_rank() OVER (ORDER BY salary DESC) = 2. They are neat, but the portable versions above run unchanged on PostgreSQL and SQLite.

Q: Why is the result NULL when the table has one row?

MAX over an empty set is NULL, and a scalar subquery that finds no row is NULL. Both solutions turn "no second salary" into the one-row NULL result the problem wants without a COALESCE.

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 DuckDB

Does DuckDB fold unquoted identifiers to lowercase like PostgreSQL?

No. DuckDB is case-insensitive for identifiers but preserves the case you wrote, so AS SecondHighestSalary comes back with its capitals. PostgreSQL would lowercase it; the quotes make the two dialects behave the same.

Is there a shorter DuckDB-only spelling?

DuckDB allows SELECT max(salary) FILTER (WHERE salary < (SELECT max(salary) FROM employee)) and arg_max, and it has QUALIFY for window filters: SELECT salary FROM (SELECT DISTINCT salary FROM employee) QUALIFY dense_rank() OVER (ORDER BY salary DESC) = 2. They are neat, but the portable versions above run unchanged on PostgreSQL and SQLite.

Why is the result NULL when the table has one row?

MAX over an empty set is NULL, and a scalar subquery that finds no row is NULL. Both solutions turn "no second salary" into the one-row NULL result the problem wants without a COALESCE.

Keep going

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

second_highest_salary.sql

DuckDB 1.5

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