Second-highest salary — interview question

Table:

SQL

CREATE TABLE employees (id int PRIMARY KEY, name text NOT NULL, salary int NOT NULL);

Write one query that returns a single column second_highest: the second-highest distinct salary. If there is no such salary (fewer than two distinct values, or no rows), the query must still return one row containing NULL.

Input and output

Output: One row, one column (NULL prints as an empty line in psql).

Examples

Input 1

CREATE TABLE employees (id int PRIMARY KEY, name text NOT NULL, salary int NOT NULL);
INSERT INTO employees VALUES (1,'Asha',50000),(2,'Ravi',70000),(3,'Meena',60000);

Output

Basic case.

Input 2

CREATE TABLE employees (id int PRIMARY KEY, name text NOT NULL, salary int NOT NULL);
INSERT INTO employees VALUES (1,'Asha',50000);

Output

(empty)

Only one salary: NULL.

Show hints

An aggregate over zero rows still returns one row.
"Highest salary below the highest salary".

Show the explanation

Take the maximum salary that is strictly below the overall maximum. MAX over an empty set yields one row with NULL, which satisfies the "no second salary" case without special handling. Duplicated top salaries are skipped automatically because of the strict <.

Alternatives: DENSE_RANK() in a subquery, or SELECT DISTINCT salary ... ORDER BY salary DESC OFFSET 1 LIMIT 1 wrapped in a scalar subquery so an empty result becomes NULL.

Original UniCircle question. Last reviewed 21 Oct 2026.