MediumWindow Functions High Frequency

Second Highest Salary / Nth Highest Salary

Frequently Asked By:AmazonMetaGoogleApple

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Write a SQL query to report the second highest distinct salary from the Employee table. If there is no second highest salary (for example, only 1 employee exists or all employees share the identical compensation), your query must return NULL instead of an empty result set.

Why Technical Interviewers Ask This

This is the canonical SQL screening question. Interviewers use it to immediately filter candidates on 3 core fundamentals: (1) handling duplicate salary values, (2) returning NULL when no row satisfies the condition, and (3) scaling from 2nd highest to Nth highest without rewriting logic.

Common Pitfalls & Interview Traps

Using LIMIT 1 OFFSET 1 directly without wrapping in a subquery or scalar SELECT — if the table has fewer than 2 rows, it returns an empty rowset (0 rows) instead of a single row containing NULL.

Using ROW_NUMBER() instead of DENSE_RANK() or DISTINCT — if two employees tie for the top salary of $200k, ROW_NUMBER() will incorrectly label the second $200k as the "second highest" salary.

Assuming salaries are non-negative or strictly integers without verifying data types.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

-- Method 1: Modern ANSI SQL using Window Functions
WITH RankedSalaries AS (
  SELECT
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
  FROM employees
)
SELECT
  (SELECT salary FROM RankedSalaries WHERE rnk = 2 LIMIT 1) AS second_highest_salary;

-- Method 2: Classical Subquery with NULL wrapper
SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Approach Breakdown

DENSE_RANK() assigns consecutive rank integers without skipping numbers when ties occur. Wrapping the lookup inside a scalar SELECT (SELECT ...) guarantees that if the WHERE rnk = 2 condition matches zero rows, the SQL engine returns a 1x1 NULL output rather than zero rows.

Edge Cases to Verbally Clarify with Your Interviewer

The table is completely empty (must yield 1 row with NULL).

The table has exactly 1 employee record (must yield NULL).

All employees earn the exact same salary (must yield NULL because no distinct second rank exists).

Multiple employees tie for 1st place and multiple tie for 2nd place.

Frequently Asked Follow-Up Questions

Why does LIMIT 1 OFFSET 1 fail test cases on LeetCode/HackerRank?

LIMIT 1 OFFSET 1 returns an empty result set (0 rows) when there are fewer than 2 rows. Most technical grading harnesses require an explicit row with a NULL value. Wrapping the query as a scalar subquery (SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1)) satisfies the NULL contract.

What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() here?

ROW_NUMBER() assigns unique sequential integers (1, 2, 3...) regardless of ties. RANK() leaves gaps (e.g. 1, 1, 3). DENSE_RANK() handles ties without gaps (1, 1, 2), ensuring the true second-highest distinct value is always assigned rank 2.