, ,

How to Select the Nth highest / lowest value from a table

I am posting this script as lot of my friends have asked about this. They are lot of ways to do this. I am showing you in two best ways.

Note:- In ‘N’ place, provide your Nth number you want to findout.

How to Select the Nth highest value from a table:-
————————————————-

select level, max(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) > sal
group by level;

How to select the Nth lowest value from a table:-
——————————————————-
select level, min(sal) from scott.emp
where level = ‘&n’
connect by prior (sal) < style=”font-weight: bold;”>N th Top Salary
——————-

select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b where a.sal <= b.sal) N th Least Salary
———————
select a.ename,a.sal
from emp a
where n = (select count(distinct(b.sal))
from emp b
where a.sal >= b.sal)
/by