nth highest salary

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
[code]
select max(sal) from emp where sal< (select max(sal) from emp where sal<(select max(sal) from emp)) [/code]
This one using nested sub sub query. In this way you select maximum upto 255 level.

select sal from (

(select distinct sal from emp where rownum<=&n order by sal desc )

)
where rownum <=1 order by sal

[QUOTE=veluru13;21661]how to retrieve nth highest salary from an emp table[/QUOTE]

thank u friends for ur replies.

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