top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration

Facebook Login
Site Registration

Select 2nd Highest salary from employee table

+6 votes
104 views
Select 2nd Highest salary from employee table
posted Nov 28, 2014 by Manikandan J

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+2 votes

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);

For more information about Test Script:http://stackoverflow.com/questions/7417415/how-to-get-second-highest-salary-employees-in-a-table

answer Nov 28, 2014 by Vrije Mani Upadhyay
Similar Questions
+1 vote

How we can get nth max salary from Employee Table (EMP_ID, Salary)

Emp_id salary
1 1000
3 2000
4 16000
2 10000
7 19000
8 12000

e.g. for 3rd max salary, salary is 12000 so answer will be emp_id : 8

Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...