← All tasksSQL · Middle

Second highest salary

Return the second highest distinct salary as one row with one column second_salary. If there's none (all salaries are equal or the table is empty), the result must be one row with NULL, not zero rows.

Tables

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

SELECT salary AS second_salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (4)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO employees VALUES (1, 'Ann', 7000), (2, 'Bob', 9000), (3, 'Cid', 5000);

-- the top salary appears twice
INSERT INTO employees VALUES (1, 'Ann', 9000), (2, 'Bob', 9000), (3, 'Cid', 6000);

-- only one distinct salary: the answer is NULL
INSERT INTO employees VALUES (1, 'Ann', 5000), (2, 'Bob', 5000);

-- empty table: still one row with NULL
SELECT 1;