How to get second-highest salary employees in a table

Question

It's a question I got this afternoon:

There a table contains ID, Name, and Salary of Employees, get names of the second-highest salary employees, in SQL Server

Here's my answer, I just wrote it in paper and not sure that it's perfectly valid, but it seems to work:

SELECT Name FROM Employees WHERE Salary = 
( SELECT DISTINCT TOP (1) Salary FROM Employees WHERE Salary NOT IN
 (SELECT DISTINCT TOP (1) Salary FROM Employees ORDER BY Salary DESCENDING)
ORDER BY Salary DESCENDING)

I think it's ugly, but it's the only solution come to my mind.

Can you suggest me a better query?

Thank you very much.

 

Answer 1

SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee); This query will give you the desired output i.e 12000, which is the second highest salary.