Solution1
In this solution first we determine the top 2 salaries from the salary column in descending order. After that we find the min salary from two salaries using the min function. That will be the second highest salary. The following query defines that:
select min(Salary) as SecondHighestSalary from UserDetail where Salary in
(select distinct TOP 2 (Salary) from UserDetail order by Salary desc)
OUTPUT
Solution2
In this solution first we determine the max 2 salaries from the salary column. After that we define a condition wherein the max salary is less than from the outer query. That will be the second highest salary. The following query defines that:
select MAX (salary ) from UserDetail where
salary not in(select MAX (salary) from UserDetail )
Solution3
This solution is similar to the previous solution. We can also write the previous query as in the following query:
select MAX (salary ) from UserDetail where
salary < (select MAX (salary) from UserDetail );
To Get Nth Highest Salary
To get a specified highest salary we use the following query:
Select min(Salary) salary from UserDetail
where Salary in (
select distinct top(n) salary from UserDetail order by Salary desc
)
Here, n species the count of the highest salaries to retrieve; if we set the value of n to 2 then we get the 2 highest salaries.
To find 2nd highest salary:
Select min(Salary) salary from UserDetail
where Salary in (
select distinct top(2) salary from UserDetail order by Salary desc
)
OUTPUT
To find the 3rd highest salary:
Select min(Salary) salary from UserDetail
where Salary in (
select distinct top(3) salary from UserDetail order by Salary desc
)
OUTPUT
To find the 4th highest salary:
Select min(Salary) salary from UserDetail
where Salary in (
select distinct top(4) salary from UserDetail order by Salary desc
)
OUTPUT