Which query returns the second highest distinct salary?
Options
- SELECT salary FROM emp ORDER BY salary DESC LIMIT 2 (returns two rows)
- SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp) (Correct option)
- SELECT MIN(salary) FROM emp WHERE salary > (SELECT AVG(salary) FROM emp)
- SELECT salary FROM emp WHERE rank = 2 ORDER BY salary DESC
Answer
SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp)
Why
The inner query finds the highest salary. The outer query finds the highest salary that is LOWER than that, which is the second highest.
The option with LIMIT 2 returns two rows (the two highest salaries), not just the second one.
Alternative: DENSE_RANK() OVER (ORDER BY salary DESC) and keep rank 2. In interviews, ask how ties should be handled.
Remember
Second highest = MAX below the MAX.