Write a SQL query to find the second highest salary from an Employees table without using LIMIT or TOP.

Answer

The most reliable way to find the second highest salary without LIMIT/TOP is a correlated subquery that counts how many distinct salaries are greater than each row's salary.

Solution

SELECT MAX(salary) AS second_highest_salary
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);

Step by Step

1. The inner subquery SELECT MAX(salary) FROM Employees finds the single highest salary in the table.
2. The outer query filters out every row equal to that maximum, leaving only salaries strictly below it.
3. Taking MAX() of what remains gives the second-highest distinct value.

Alternative Methods

Using DENSE_RANK()

SELECT salary AS second_highest_salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM Employees
) ranked
WHERE rnk = 2;

DENSE_RANK() is preferred over RANK() here because RANK() would skip a rank number after a tie, while DENSE_RANK() correctly treats tied top salaries as rank 1 and the next distinct value as rank 2.

Using a Correlated Subquery with COUNT

SELECT DISTINCT salary AS second_highest_salary
FROM Employees e1
WHERE 2 = (
  SELECT COUNT(DISTINCT salary) FROM Employees e2 WHERE e2.salary >= e1.salary
);

Key Exam Traps

• Duplicate top salaries: If two employees share the highest salary, a naive "second row" approach (like OFFSET 1) returns a duplicate of the top salary, not the true second-distinct value. The MAX(salary) WHERE salary < overall max approach avoids this entirely.
• Empty result is valid: If every employee has the same salary, this query correctly returns NULL - a common thing interviewers ask you to explain.
• RANK() vs DENSE_RANK(): Using plain RANK() on data with tied top salaries silently gives the wrong answer - this is one of the most common trick points in this exact question.