How to get second-highest salary employees in a table

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 RnkFROM Employees)SELECT NameFROM TWHERE Rnk=2;

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

SELECT NameFROM   EmployeesWHERE  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 EmployeesSELECT TOP 1000000 s1.name,                   abs(checksum(newid()))FROM   sysobjects s1,       sysobjects s2CREATE NONCLUSTERED INDEX ix  ON Employees(Salary)SELECT NameFROM   EmployeesWHERE  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 NameFROM   TWHERE  Rnk = 2;SELECT NameFROM   EmployeesWHERE  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 NameFROM   EmployeesWHERE  Salary = (SELECT TOP 1 Salary                 FROM   (SELECT TOP 2 Salary                         FROM   Employees                         ORDER  BY Salary DESC) sel                 ORDER  BY Salary ASC)  

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

Try like this..

This might help you

SELECT       MIN(SALARY) FROM       EMP WHERE       SALARY in (SELECT                       DISTINCT TOP 2 SALARY                  FROM                       EMP                  ORDER BY                       SALARY DESC                )

We can find any nth highest salary by putting n (where n > 0) in place of 2

Example for 5^th highest salary we put n = 5