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

To get the names of the employees with the 2nd highest distinct salary amount you can use.

;WITH T AS ( SELECT *,        DENSE_RANK() OVER (ORDER BY Salary Desc) AS Rnk FROM Employees ) SELECT Name FROM T WHERE Rnk=2; 

If Salary is indexed the following may well be more efficient though especially if there are many employees.

SELECT Name FROM   Employees WHERE  Salary = (SELECT MIN(Salary)                  FROM   (SELECT DISTINCT TOP (2) Salary                          FROM   Employees                          ORDER  BY Salary DESC) T); 

Test Script

CREATE TABLE Employees   (      Name   VARCHAR(50),      Salary FLOAT   )  INSERT INTO Employees SELECT TOP 1000000 s1.name,                    abs(checksum(newid())) FROM   sysobjects s1,        sysobjects s2  CREATE NONCLUSTERED INDEX ix   ON Employees(Salary)  SELECT Name FROM   Employees WHERE  Salary = (SELECT MIN(Salary)                  FROM   (SELECT DISTINCT TOP (2) Salary                          FROM   Employees                          ORDER  BY Salary DESC) T);  WITH T      AS (SELECT *,                 DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk          FROM   Employees) SELECT Name FROM   T WHERE  Rnk = 2;  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 DESC)                  ORDER  BY Salary DESC)  SELECT Name FROM   Employees WHERE  Salary = (SELECT TOP 1 Salary                  FROM   (SELECT TOP 2 Salary                          FROM   Employees                          ORDER  BY Salary DESC) sel                  ORDER  BY Salary ASC)   

Answer 2

 SELECT * from Employee  WHERE Salary IN (SELECT MAX(Salary)                   FROM Employee                   WHERE Salary NOT IN (SELECT MAX(Salary)                                        FFROM employee)); 

Try like this..