write a query to select second highest salary of the employee from the employee table?

 Posted by Peeyushkumar2 on 3/24/2013 | Category: Sql Server Interview questions | Views: 11812 | Points: 40

select * from employee

select top 1 salary from
(select top 2 salary from employee order by(salary) desc )a
order by(salary)

It first select top 2 salary from table and arrange them in descending order now we have the second highest salary in second number then select top 1 by ascending it that is the second highest salary.

Asked In: few interviews | Alert Moderator 

Comments or Responses

Posted by: Jitendrasoft09 on: 3/27/2013 | Points: 10
If the requirement for only find out the 2nd Highest salary, we can use the following code-

select Max(salary) from Employee where salary not in (select Max(salary) from Employee )

If the requirement for nth Highest salary with unique value, we can use following code-

select Top 1 salary from (select distinct Top 2 salary from Employee order by salary desc) a
order by salary
Posted by: aswinialuri-19361 on: 3/27/2013 | Points: 10
for second highest salary in employee table
select max(salary) from employee where salary<(select max(salary) from employee)
it is very short and easy to understand for any one

Posted by: Chvrsri on: 3/27/2013 | Points: 10
Hi Aswinialuri & Jitendrasoft09,

Thank you so much for the responses. This place is dedicated only to post the responses of the above mentioned question. If you find a better way of answerring it kindly get back to us by posting a new question then we shall review it and approve.

Thank you so much for your co-operation

Moderator Interview Section
Posted by: Satyapriyanayak on: 3/27/2013 | Points: 10
select distinct(salary) from employee A where 2=(select count(distinct(salary)) from employee B where A.salary<=B.salary)
Posted by: Pkanwar on: 3/30/2013 | Points: 10

For getting Second Highest Salary:

Select min(salary) from Employee where salary in (select top 2 salary from Employee order by salary descending )

First In subquery will be select top 2 salary order by descending, then in Top 2 salary will be select the minimum salary, which would be second highest salary.


Login to post response

More Interview Questions by Peeyushkumar2