Database & SQL Top Interview Question

SQL Query to Find the Second Highest Salary (3 Methods Explained)

Error Message
Write a SQL query to report the second highest salary from the Employee table.
Quick Fix
SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

Why Does This Happen?

Classic SQL interview challenge testing subqueries, window functions, and NULL handling.

Step-by-Step Solution

1
Method 1: Subquery with MAX() — safest for NULLs
Find the maximum salary that is less than the overall max:
Terminal / Code 
SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);
Returns NULL automatically when fewer than 2 distinct salaries exist — correct per LeetCode.
2
Method 2: DISTINCT + ORDER BY + LIMIT OFFSET
Terminal / Code 
SELECT (
  SELECT DISTINCT salary
  FROM Employee
  ORDER BY salary DESC
  LIMIT 1 OFFSET 1
) AS SecondHighestSalary;
Works in MySQL, PostgreSQL, and SQLite.
3
Method 3: DENSE_RANK() Window Function
Handles ties gracefully — two people with the same top salary both get rank 1:
Terminal / Code 
WITH Ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employee
)
SELECT MAX(salary) AS SecondHighestSalary
FROM Ranked
WHERE rnk = 2;
DENSE_RANK is the production-grade enterprise SQL approach.

Common Mistakes to Avoid

Using LIMIT 1 OFFSET 1 without DISTINCT — if two employees share the top salary, you get the top salary again instead of the second.
Using ROW_NUMBER() instead of DENSE_RANK() — ROW_NUMBER gives unique ranks to tied rows.

Frequently Asked Questions

How do I find the Nth highest salary?
Generalise Method 3: WHERE rnk = N, or Method 2: LIMIT 1 OFFSET N-1.

Learn to Write Code That Doesn't Break

DoxaTech Hub's 10-month programme teaches you Git internals, Linux systems, C, Python, SQL, Docker, and React from first principles — so you spend less time fixing errors and more time shipping.

Start Learning →