Write a SQL Server query to find the second highest salary.

Short answer: Use DENSE_RANK() or OFFSET/FETCH. Prefer DENSE_RANK when ties matter. Avoid brittle MAX(salary) WHERE salary < MAX(salary) without discussing ties.

Problem statement

Employees(EmpId, Name, Salary). Return the second highest distinct salary.

Interview approach

1. Ask whether “second highest” means distinct salaries or second row after sort.
2. Show DENSE_RANK for distinct ranks.
3. Mention TOP 1 ... WHERE Salary < (SELECT MAX(Salary) ...) as older style.

Sample solution

-- Option A: DENSE_RANK (handles ties cleanly)
SELECT Salary
FROM (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk
    FROM Employees
) t
WHERE rnk = 2;

-- Option B: OFFSET/FETCH (SQL Server)
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

Edge cases to mention

• Fewer than 2 distinct salaries
• NULL salaries
• Multiple employees sharing the top salary

Common follow-ups

• Nth highest salary
• Second highest per department

Say DENSE_RANK vs RANK out loud — panels love that distinction.