Finding the second highest salary is a classic SQL interview question. It’s used for ranking employees, bonus calculations, and salary analysis.
Find the max salary less than the highest: SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);
Get the second-highest directly: SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1; Works in MySQL and PostgreSQL.
Exclude salaries with a higher value: SELECT Salary FROM Employee e1 WHERE 1 = (SELECT COUNT(DISTINCT Salary) FROM Employee e2 WHERE e2.Salary >= e1.Salary);
Assign ranks and pick rank 2: SELECT Salary FROM (SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rk FROM Employee) AS t WHERE rk = 2
These methods handle ties. For example, if two employees share the highest salary, the next distinct value is the second-highest.
Know multiple methods—subquery, LIMIT, and window functions. Choose the one best suited to your SQL dialect and explain your logic clearly in interviews