Click here to hide categories Click here to show left categories

User: Home          welcome : Guest          Log In / Register here     




.NET and SQL Server interview questions - Can you get second highest salary from the table?

Answer:

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 two same records as second highest salary so the result set will look like below output table.

Output:-
 
Emp_NameEmp_Salary
Sham15000
        
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.

 

Please click here to see more .NET and SQL Server interview questions

Regards,

Visit Authors blog for more .NET and SQL Server interview questions

Share this article   |    Print    |    Article read by 4139 times
Author:
Shivprasad koirala Koirala
I am a Microsoft MVP for ASP/ASP.NET and currently a CEO of a small E-learning company in India. We are very much active in making training videos , writing books and corporate trainings. Do visit my site http://www.questpond.com for .NET, C# , design pattern , WCF , Silverlight , LINQ , ASP.NET , ADO.NET , Sharepoint , UML , SQL Server training and Interview questions and answers
Related Articles:
Related Interview Questions: