SQL Statement to get the 2nd highest salaried/paid Employee.

Best Solution:

select *
from emp
where sal =
(
select max(sal)
from emp
where level=2
connect by prior sal>sal
)
;

SQL Statement to get Nth highest salaried/paid employee(s)

Best.

select *
from emp
where sal =
(
select max(sal)
from emp
where level=&level
connect by prior sal>sal
)
;

Other Solutions
1.)

select *
from emp
where sal=
(
select sal from
(
select rownum Row1,sal
from (
select distinct(sal) sal
from emp
order by sal desc
)
)
where row1=&level
)

2.)

Select empno,ename,job,sal,comm,deptno
from (
select em.*,dense_rank() over
(
partition by grp order by sal desc
) as topsal
from (
select 1 as grp,e.* from emp e
) em
)
where topsal=&topsal

add to del.icio.us saved by 0 users