.NET and SQL Server interview questions – How will you obtain second highest salary from the table?

Answer:

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

mp_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_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.
Please click here to see more .NET and SQL Server interview questions

Regards,

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