I want to get details of the employee who is having nth highest sal from emp table.
select * from (select e.*,rownum rn from emp e order by sal desc) where rn=5;I used above query but it is not giving sal in decreasing order.
How can we get this in order or sal and in that 5th highest sal.
You need to use row_number function instead of rownum. Because in your query first it will select rownum after that result is ordered based on ur order by statement.To avoid this you need to use one more subquery otherwise you can use below query.
select * from (select e.*,row_number() over(order by sal desc) row_n from emp e )where row_n in (5,8);