The second-highest salary

Find the second-highest different salary. Two people sharing the top salary do not make it the second highest: 900, 900, 700 gives 700. If there is no second salary (everyone earns the same), the answer is NULL.

What to build

A query that finds the second-highest different salary.

Requirements

• Find the highest salary with a subquery.
• Take the largest salary that is less than it.
• Return NULL when there is no second salary.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);

Output

One row with one column, second_highest. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

The table has at least one employee.

Example

When a test starts with:

INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000);

your SQL should print:

second_highest
--------------
65000
(1 row)

Why it works

Every salary below the maximum is a candidate, and the largest of them is the second-highest different value. MAX of no rows is NULL.

Examples

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Farhana', 'Sales', 40000),
  (2, 'Imran', 'IT', 65000),
  (3, 'Joya', 'Sales', 38000),
  (4, 'Kamal', 'IT', 72000);

second_highest
--------------
65000
(1 row)

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL,
  salary INTEGER NOT NULL
);
INSERT INTO employees VALUES
  (1, 'Mitu', 'IT', 90000),
  (2, 'Nayeem', 'IT', 90000),
  (3, 'Orin', 'IT', 70000);

second_highest
--------------
70000
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Lesson: Subqueries and WITH →