.NET and SQL Server interview questions - Select second highest salary from the table?

 Posted by ArticlesMaint on 4/25/2011 | Category: Sql Server Interview questions | Views: 3354


Let's us assume that we have the following table of Employee.



























Emp_Id
Emp_Name
Emp_Salary
1
Shiv
17000
2
Raju
13500
3
Sham
15000
4
Moosa
11000
5
Feroz
12000

Now we want to find out second highest salary from Employee table, as you see that the second highest salary is 15000 and we want to display the same.

Query:-
SELECT Emp_Name,Emp_Salary
FROM Employee e1
WHERE
2 = (SELECT COUNT(DISTINCT (e2.Emp_Salary))FROM Employee e2 WHERE e2.Emp_Salary >= e1.Emp_Salary)

The above employee table contains the second highest salary as 15000 therefore the result set will look like below output table.

Output:-








Emp_Name Emp_Salary
Sham 15000
        
If the table contains two or more same record of salary which is the second highest salary then the query will give you all the record of second highest salary as you see in the above output table.


Regards,



Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Login to post response