how to retrieve nth highest salary from an emp table
how to retrieve nth highest salary from an emp table
select level, max('col_name') from my_table
where level = '&n'
connect by prior ('col_name') > 'col_name')
group by level;
example to find 4th highest salary
select level, max(sal) from emp
where level=4
connect by prior sal > sal
group by level
You can also try
This one using nested sub sub query. In this way you select maximum upto 255 level.Code:select max(sal) from emp where sal< (select max(sal) from emp where sal<(select max(sal) from emp))
select sal from (
(select distinct sal from emp where rownum<=&n order by sal desc )
)
where rownum <=1 order by sal
for this we can give the rank for each salary.first highest sal is rank 1 so on.
here is the eg:
select a.ename,a.sal,b.rnk from emp a,(select rank() over(order by sal desc
nulls last) rnk from emp) b where b.rnk=&rnk;
select emp_name,sal from (
select emp_name, sal, rank() over (order by sal desc nulls last ) rn from emp) where rn= &var