nth highest salary

[veluru13]

how to retrieve nth highest salary from an emp table

[susarlasireesha]

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

[debasisdas]

You can also try

select max(sal) from emp where sal< (select max(sal) from emp where sal<(select max(sal) from emp))

This one using nested sub sub query. In this way you select maximum upto 255 level.

[susantaghosh]

select sal from (

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

)
where rownum <=1 order by sal

[veluru13]

how to retrieve nth highest salary from an emp table

thank u friends for ur replies.

[ashalalaxmi2002]

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;

[expertsharingdotcom]

select emp_name,sal from (
select emp_name, sal, rank() over (order by sal desc nulls last ) rn from emp) where rn= &var