FIND 2nd/nth HIGHEST and 2nd/nth LOWEST VALUES

FIND 2nd/nth HIGHEST and 2nd/nth LOWEST VALUES



nth highest value

select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level;


2nd highest value
select level, max(sal) from emp where level=2 connect by prior sal > sal group by level;

or 

select max(phone) from uk.dept where phone not in (select max(phone) from uk.dept);

select * FROM (
select <COLUMN_NAME>
,rank() over (order by <COLUMN_NAME> DESC) ranking
from <TABLE_NAME>
)

WHERE ranking = N;

nth lowest value

select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level;


2nd lowest value
select level, max(sal) from emp where level=2 connect by prior sal < sal group by level;

select * FROM (
select <COLUMN_NAME>
,rank() over (order by <COLUMN_NAME>) ranking
from <TABLE_NAME>
)

WHERE ranking = N;