Find nth highest salary without using top in SQL
Here i would use the CTC (common table expression).and i will use a one building function like Dense_Rank() to find the highest salary without using top.
Example:- with result as
(
select salary, dense_rank() over (order by salary desc) as denserank
from employee
)
select salary
from result
where result.denserank= n ( 1st,2st,3st.........nth)
Here we can get nth highest salary by using this query but end of the query we need to put the nth place which we want like if we want 1 highest salary we need to put 1 and 2 highest salary then put 2 like that we can find nth highest salary in this query.
But in our table duplicate records are there then above query will not give the perfect result then we should go for the next query. but here we need to use top () function
Example:- with result as
(
select salary, dense_rank() over (order by salary desc) as denserank
from employee
)
select top 1 salary
from result
where result.denserank= n ( 1st,2st,3st.........nth)
Here i have another method to find nth highest salary without using top () function.
Example:- select * from employee
order by salary desc offset n
fetch next 1 only
Here we used the offset which mean it will left the record and it will give the next record like we put offset 3 then it will left the top three (3) record and it will give the 4th record. in this process we can also find the nth highest salary without using top () function.
